update table with values from other table

By | 17 June, 2009

I ran into a problem where i had to update two values on a table. One was fixed, but the other depended on a value stored on a second table. This is how its done.


id_cli   test_cut

1          15

2          56


id    num_of        job    test_cut    id_client

1     20090464    34      0               2

2     20081012    200    0               1

I needed to update the value of the fields “job” and “test_cut” on Table B, but the value i wanted to put on “test_cut” needs to come from Table A, linked by field “id_cli”. Confusing?

update TableB Tb,TableA Ta set Tb.job=’550′,Tb.test_cut=Ta.test_cut where Tb.num_of=’20090494′ and Ta.id_cli=Tb.id_cli

This update joins the two tables by the field id_cli, fetches the test_cut on TableA and updates fields test_cut and job_meio on TableB

This will turn TableB into:

id    num_of        job      test_cut    id_client

1     20090464    550 56 2

2     20081012    200     0               1