Sometime We need to Update one table from another table. Suppose we have a table named property which has one column named value that should be a double value, but somehow we changed it to decimal (10,2) so now it will truncate from the third decimal point if there are some. But once we need to back the thing and we have the backup of old property table so what can we do? We can add the old table named property_old and then change the column datatype. Now needs to update the value from old table to new table.
With Mysql 4+ we can easily do that:
UPDATE property pn
INNER JOIN property_old po
ON pn.id = po.id
SET pn.value = po.value
You can use LEFT JOIN/RIGHT JOIN also here. And also you can update the values of more than one table those are joined.
UPDATE table1 t1
INNER JOIN table2 t2
ON t1.id = t2.id
SET t1.value = t2.value, t2.colname=anything
Isn’t easy? Yeah it is very easy to update cross table update in mysql.