Arquiva-me

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.

TableA

id_cli   test_cut

1          15

2          56

TableB

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_meio=’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

If we want to make an update to a table, but have to condition that update with values from a second one, we do something like this:

TABLE_1
id_t1 name fee
1 john 100
2 rita 100
3 peter 100

TABLE_2
id_t2 id_t1 type
1 1 single
2 2 single
3 3 married

If we want to update the field “fee” from TABLE_1, for example, to make single people pay more than married people (i know, stupid example, but bear with me), we can do this:

update TABLE_1 t1,TABLE_2 t2 set t1.fee=200 where t1.id_t1=t2.id_t1 and t2.type='single'


this is going to update all the rows in TABLE_1 that belong to “single” people, resulting in:

TABLE_1
id_t1 name fee
1 john 200
2 rita 200
3 peter 100

The update is an inner join with the comma operator.

Heres a link to the MySQL manual page on the update command as a reference. Has some nice examples:

http://dev.mysql.com/doc/refman/5.0/en/update.html