我正在尝试根据另一个 MySQL 表的信息更新一个 MySQL 表.
I'm trying to update one MySQL table based on information from another.
我的原始
表看起来像:
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
我想根据 value<用
original
中的 id
更新 tobeupdated
中的 id
/code>(存储在 VARCHAR(32)
字段中的字符串).
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
这会最大化我的 CPU 并最终导致只执行一小部分更新的超时(有几千个值要匹配).我知道按 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?
我尝试了 MySQL - 如何我可以用另一个表中的值更新一个表吗?,但这并没有真正的帮助.有什么想法吗?
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!
UPDATE tobeupdated
INNER JOIN original ON (tobeupdated.value = original.value)
SET tobeupdated.id = original.id
那应该可以,而且它确实在做你的事情.但是,我更喜欢连接的JOIN"语法而不是多个WHERE"条件,我认为它更容易阅读
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
至于运行缓慢,表有多大?您应该在 tobeupdated.value
和 original.value
As for running slow, how large are the tables? You should have indexes on tobeupdated.value
and original.value
我们也可以简化查询
UPDATE tobeupdated
INNER JOIN original USING (value)
SET tobeupdated.id = original.id
USING
是当连接的两个表具有相同的名称 key
(例如 id
)时的简写.即 equi-join - http://en.wikipedia.org/wiki/Join_(SQL)#Equi-join
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
这篇关于使用另一个 MySQL 表的值更新一个 MySQL 表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!