使用 LEFT JOIN 更新 MySQL 中的多个表

2021-11-20 00:00:00 sql-update 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 to NOT 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.

相关文章