问题描述
我有两个表，想更新 T1 中左联接中所有行的字段.
I have two tables, and want to update fields in T1 for all rows in a LEFT JOIN.
举个简单的例子，更新以下结果集的所有行:
For an easy example, update all rows of the following result-set:
SELECT T1.* FROM T1 LEFT JOIN T2 ON T1.id = T2.id WHERE T2.id IS NULL
MySQL 手册指出:
多表 UPDATE 语句可以使用 SELECT 语句中允许的任何类型的连接，例如 LEFT JOIN.
Multiple-table UPDATE statements can use any type of join allowed in SELECT statements, such as LEFT JOIN.
但我在记录的多表更新中找不到正确的语法.
But I cannot find the proper syntax for doing that in the documented multiple-tables UPDATE.
正确的语法是什么?
推荐答案
UPDATE t1
LEFT JOIN
t2
ON t2.id = t1.id
SET t1.col1 = newvalue
WHERE t2.id IS NULL
请注意，对于 SELECT 使用 NOT IN/NOT EXISTS 语法会更有效:
Note that for a SELECT it would be more efficient to use NOT IN / NOT EXISTS syntax:
SELECT t1.*
FROM t1
WHERE t1.id NOT IN
(
SELECT id
FROM t2
)
有关性能详细信息，请参阅我博客中的文章:
See the article in my blog for performance details:
- 查找不完整的订单:
LEFT JOIN与NOT IN 的性能对比
- Finding incomplete orders: performance of
LEFT JOINcompared toNOT IN
不幸的是，MySQL 不允许在 UPDATE 语句的子查询中使用目标表，这就是为什么你需要坚持效率较低的 LEFTJOIN 语法.
Unfortunately, MySQL does not allow using the target table in a subquery in an UPDATE statement, that's why you'll need to stick to less efficient LEFT JOIN syntax.
这篇关于使用 LEFT JOIN 更新 MySQL 中的多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！