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
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_idAnd 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