用另一个MySQL值更新一个MySQL表 [英] Update one MySQL table with values from another

问题描述

我正在尝试根据另一个MySQL表的信息来更新它.

I'm trying to update one MySQL table based on information from another.

我的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

我想根据value(存储在VARCHAR(32)字段中的字符串)，用original中的id更新tobeupdated中的id.

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)的简写.即等值联接- 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表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！