Coder Perfect

Replace the values in one MySQL table with those from another.


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

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)

When both tables in a join contain the same named key, such as id, the term USING is used. (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