Update one MySQL table with values from another

Question

I'm trying to update one MySQL table based on information from another.

My original table looks like:

id | value ------------ 1  | hello 2  | fortune 3  | my 4  | old 5  | friend 

And the tobeupdated table looks like:

uniqueid | id | value --------------------- 1        |    | something 2        |    | anything 3        |    | old 4        |    | friend 5        |    | fortune 

I want to update id in tobeupdated with the id from original based on value (strings stored in VARCHAR(32) field).

The updated table will hopefully look like:

uniqueid | id | value --------------------- 1        |    | something 2        |    | anything 3        | 4  | old 4        | 5  | friend 5        | 2  | fortune 

I have a query that works, but it's very slow:

UPDATE tobeupdated, original SET tobeupdated.id = original.id WHERE tobeupdated.value = original.value 

This maxes out my CPU and eventually leads to a timeout with only a fraction of the updates performed (there are several thousand values to match). I know matching by value will be slow, but this is the only data I have to match them together.

Is there a better way to update values like this? I could create a third table for the merged results, if that would be faster?

I tried MySQL - How can I update a table with values from another table?, but it didn't really help. Any ideas?

Thanks in advance for helping a MySQL novice!

Answer 1

UPDATE tobeupdated INNER JOIN original ON (tobeupdated.value = original.value) SET tobeupdated.id = original.id 

That should do it, and really its doing exactly what yours is. However, I prefer 'JOIN' syntax for joins rather than multiple 'WHERE' conditions, I think its easier to read

As for running slow, how large are the tables? You should have indexes on tobeupdated.value and original.value

EDIT: we can also simplify the query

UPDATE tobeupdated INNER JOIN original USING (value) SET tobeupdated.id = original.id 

USING is shorthand when both tables of a join have an identical named key such as id. ie an equi-join - http://en.wikipedia.org/wiki/Join_(SQL)#Equi-join