使用 WHERE 子句的多个表的 MySQL UPDATE 语法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15037883/
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 syntax with multiple tables using WHERE clause
提问by Ben
Case:
案件：
How to update table1with data from table2where idis equal?
如何更新table1从数据table2哪里id是平等的吗？
Problem:
问题：
When I run the following update statement, it updates all the records in table1(even where the idfield in table1does not exist in table2).
当我运行以下更新语句时，它会更新中的所有记录table1（即使 中的id字段table1不存在table2）。
How can I use the the multiple update table syntax, to update ONLY the records in table1ONLY where the idis present in table2and equal?
我如何使用的多个更新表的语法，在仅更新记录table1只有在id存在于table2和平等的吗？
UPDATE table1,table2
SET table1.value=table2.value
WHERE table2.id=table1.id
Thanks in advance.
提前致谢。
回答by John Woo
here's the correct syntax of UPDATEwith join in MySQL
这是UPDATEwith join in的正确语法MySQL
UPDATE table1 a
INNER JOIN table2 b
ON a.ID = b.ID
SET a.value = b.value
回答by peterm
EDITFor MySql it'll be
编辑对于 MySql 它将是
UPDATE table1 t1 INNER JOIN
table2 t2 ON t2.id = t1.id
SET t1.value = t2.value
Original answerwas for SQL Server
原始答案是针对 SQL Server
UPDATE table1
SET table1.value = table2.value
FROM table1 INNER JOIN
table2 ON table2.id=table1.id
回答by Lingasamy Sakthivel
You can try this:
你可以试试这个：
UPDATE TABLE1
SET column_name = TABLE2.column_name
FROM TABLE1, TABLE2
WHERE TABLE1.id = TABLE2.id
回答by jmontross
UPDATE table1
SET table1.value = (select table2.value
WHERE table2.id=table1.id)
