Update one MySQL table with values from another

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
----------开发者_Go百科-----------
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?

---

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

---

It depends what is a use of those tables, but you might consider putting trigger on original table on insert and update. When insert or update is done, update the second table based on only one item from the original table. It will be quicker.