update table with join condition from different table

By | 26 February, 2009

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:

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

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:

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: