使用另一个 MySQL 表的值更新一个 MySQL 表

2021-11-20 00:00:00 sql mysql

我正在尝试根据另一个 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

相关文章