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: