使用 LEFT JOIN 更新 MySQL 中的多个表
我有两个表,想更新 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 JOIN
compared 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.
相关文章