Problem
I’m attempting to update one MySQL table with data from another.
This is how the original table appears:
id | value
------------
1 | hello
2 | fortune
3 | my
4 | old
5 | friend
And here’s how the to-be-updated table looks:
uniqueid | id | value
---------------------
1 | | something
2 | | anything
3 | | old
4 | | friend
5 | | fortune
I want to replace the original id in tobeupdated with the value (strings contained in VARCHAR(32) field).
Hopefully, the new table will look like this:
uniqueid | id | value
---------------------
1 | | something
2 | | anything
3 | 4 | old
4 | 5 | friend
5 | 2 | fortune
I’ve got a query that works, but it’s quite slow:
UPDATE tobeupdated, original
SET tobeupdated.id = original.id
WHERE tobeupdated.value = original.value
This causes my CPU to overheat, resulting in a timeout with only a percentage of the updates completed (there are several thousand values to match). I realize that matching by value will take a long time, but this is the only information I have to put them together.
Is there a more efficient approach to update these values? If it’s faster, I could make a third table for the combined results.
I attempted MySQL – How can I update a table with values from another table?, but it was ineffective. Do you have any suggestions?
Thank you in advance for your assistance to a MySQL newbie!
Asked by Superangel
Solution #1
UPDATE tobeupdated
INNER JOIN original ON (tobeupdated.value = original.value)
SET tobeupdated.id = original.id
That should suffice, and it’s essentially doing the same thing as yours. However, I think the ‘JOIN’ syntax is easier to read than many ‘WHERE’ conditions for joins.
How big are the tables if they’re going to be slow? On tobeupdated.value and original.value, you should have indexes.
EDIT: We can also make the question simpler.
UPDATE tobeupdated
INNER JOIN original USING (value)
SET tobeupdated.id = original.id
When both tables in a join contain the same named key, such as id, the term USING is used. http://en.wikipedia.org/wiki/Join (SQL)#Equi-join is an example of an equi-join.
Answered by wired00
Solution #2
It depends on what the tables are used for, however you might consider adding an insert and update trigger to the original table. Once the insert or update is complete, only one item from the original table should be used to update the second table. It will be less time consuming.
Answered by firegnom
Post is based on https://stackoverflow.com/questions/5727827/update-one-mysql-table-with-values-from-another