I'm trying to update one MySQL table based on information from another.
我试图根据另一个MySQL表的信息更新一个MySQL表。
My original table looks like:
我的原始表是这样的:
id | value
------------
1 | hello
2 | fortune
3 | my
4 | old
5 | friend
And the tobeupdated table looks like:
tobeupdates表为:
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).
我想基于value(存储在VARCHAR(32)字段中的字符串)在tobeupdate中使用id更新id。
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.
这将使我的CPU达到最大值，并最终导致仅执行了一小部分更新的超时(需要匹配数千个值)。我知道按值匹配会很慢，但这是我唯一需要将它们匹配在一起的数据。
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 -如何用另一个表的值更新一个表?，但这并没有真正的帮助。什么好主意吗?
Thanks in advance for helping a MySQL novice!
预先感谢帮助MySQL新手!
2 个解决方案
#1
166
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
这应该可以做到，而且它确实做到了你的。但是，我更喜欢用“JOIN”语法来表示连接，而不是使用多个“WHERE”条件，我认为这样更容易阅读
As for running slow, how large are the tables? You should have indexes on tobeupdated.value and original.value
至于慢速运行，表有多大?您应该有更新的索引。价值和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
当连接的两个表都有一个相同的命名键(如id. ie, equi-join - http://en.wikipedia.org/wiki/Join_(SQL)# equi-join)时，使用是一种简写
#2
0
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.
这取决于这些表的用途，但是您可以考虑在插入和更新时将触发器放在原始表上。当插入或更新完成时，根据原始表中的一项更新第二个表。它将更快。
#1
166
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
这应该可以做到，而且它确实做到了你的。但是，我更喜欢用“JOIN”语法来表示连接，而不是使用多个“WHERE”条件，我认为这样更容易阅读
As for running slow, how large are the tables? You should have indexes on tobeupdated.value and original.value
至于慢速运行，表有多大?您应该有更新的索引。价值和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
当连接的两个表都有一个相同的命名键(如id. ie, equi-join - http://en.wikipedia.org/wiki/Join_(SQL)# equi-join)时，使用是一种简写
#2
0
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.
这取决于这些表的用途，但是您可以考虑在插入和更新时将触发器放在原始表上。当插入或更新完成时，根据原始表中的一项更新第二个表。它将更快。