带有子选择的 MySQL 更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6466801/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL update with sub select
提问by geoffs3310
I have a table lets call it table1, and the data in two of the columns has been destroyed.
我有一个表，我们称之为 table1，其中两列中的数据已被破坏。
Fortunately I have an old backup of the table.
幸运的是我有一个旧的表备份。
In the old backup the values for the two columns in question are correct but the rest of the columns are outdated so I can't simply import the whole dump.
在旧备份中，有问题的两列的值是正确的，但其余列已过时，因此我不能简单地导入整个转储。
So instead I've imported it into another table which I'll call table2. Each record has an id which is the same in both tables.
所以相反，我将它导入到另一个表中，我将其称为 table2。每条记录都有一个在两个表中相同的 id。
So basically I need a query that will go through each record in table1 and update column1 and column2 with the corresponding values from table2.
所以基本上我需要一个查询，该查询将遍历 table1 中的每条记录，并使用 table2 中的相应值更新 column1 和 column2。
回答by niktrs
Original table is table1 and backup table is table2
原表为table1，备份表为table2
UPDATE table1 t1 JOIN table2 t2 ON t1.id = t2.id
SET t1.col1 = t2.col1, t1.col2 = t2.col2, ...
回答by Bohemian
This will work on all flavours of SQL database:
这将适用于所有类型的 SQL 数据库：
update table1 t set
column1 = (select column1 from old_table where id = t.id),
column2 = (select column2 from old_table where id = t.id);
There's no need for any special/aggregate functions, because the id match will hit exactly one row.
不需要任何特殊/聚合函数，因为 id 匹配将恰好命中一行。
回答by danyolgiax
I think you can try something like this:
我想你可以尝试这样的事情：
UPDATE table1 SET table1field = (SELECT MAX(table2.table2field) FROM table2 WHERE table1.table1field = table2.table2field)
