用来自另一个 MySQL 表的值更新一个 MySQL 表

我试图更新一个 MySQL 表根据信息从另一个。

我的 original表看起来像:

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

tobeupdated表看起来是这样的:

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

我想更新 tobeupdated中的 id与 original中的 id基于 value(字符串存储在 VARCHAR(32)字段中)。

希望更新后的表格如下:

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

我有一个很有效的查询，但是速度很慢:

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

这会使我的 CPU 达到最大值，并最终导致超时，只执行了一小部分更新(有几千个值要匹配)。我知道用 value进行匹配会很慢，但这是我唯一能将它们匹配在一起的数据。

有没有更好的方法来更新这样的值？我可以为合并的结果创建第三个表，如果这样更快的话？

我试过 MySQL-如何使用其他表中的值更新表？，但没什么用，有什么办法吗？

提前感谢帮助 MySQL 新手！

134525 次浏览
小开

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.

小开
最佳答案 
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


提交答案