Update Table Value From Another Table

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.

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: