Update one MySQL table with values from another

yhived7q 于 2022-12-22 发布在 Mysql

关注(0)|答案(2)|浏览(136)

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?

mysql

来源：https://stackoverflow.com/questions/5727827/update-one-mysql-table-with-values-from-another

2条答案

按热度按时间

js5cn81o1#

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

赞(0）回复(0）举报 2022-12-22

o7jaxewo2#

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.

赞(0）回复(0）举报 2022-12-22

我来回答

Update one MySQL table with values from another

2条答案

相关问题

热门标签

最新问答