Update multiple tables from a column; MySQL

Update multiple tables from a column is something that does not happen very often. Personally I never had to do it. I came across this question from a friend. He is programming something, and he had the following situation:

field_data_body
+----------+---------------------+
| entry_id | body_value          |
+----------+---------------------+
|  1009    | content             |
+----------+---------------------+
|  1020    |                     |
+----------+---------------------+
|  1025    | more content        |
+----------+---------------------+


i_posts
+------+---------------+
|  id  |     html      |
+------+---------------+
| 1009 |               |
+------+---------------+
| 1020 |               | 
+------+---------------+
| 1025 |               | 
+------+---------------+

From this two tables he wanted to get i_posts updated with information from field_data_body’s body_value field. In other words write this:

 
i_posts
+----------+---------------------+
| id       | html                |
+----------+---------------------+
|  1009    | content             |
+----------+---------------------+
|  1020    |                     |
+----------+---------------------+
|  1025    | more content        |
+----------+---------------------+
MySQL

MySQL

He, was trying to update the body_value column into the html column. he did not know how to do the update. He had the same ids in i_posts and field_data_body. However in the field_data_body table it doesn’t have content for every single entry_id.

His question was “Is there a way to move the body_value into the html column while keeping the correct id?”

Well the answer is quite simple, but as I had never done such a thing. Fortunately Google had the answer. MySQL supports update from a table to another, in his case was something like this:

UPDATE i_posts AS i, field_data_body AS f SET i.html = f.body_value WHERE i.id =f.entry_id

And that is it:

UPDATE tableFrom [AS x], tableTo [AS y] 
SET x.fieldTo = y.FieldFrom
WHERE x.reference_value = y.reference_value

Reference value could be the common ID in both tables.

That is it for today, hope it works for you

Posted in Databases, Development, MySQL, Tips

Leave a Reply

Your email address will not be published. Required fields are marked *

*