mysql可以使用left连表更新么,使用LEFT JOIN更新MySQL中的多个表

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

The MySQL manual states that:

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.

What is the proper syntax?

解决方案UPDATE t1

LEFT JOIN

t2

ON t2.id = t1.id

SET t1.col1 = newvalue

WHERE t2.id IS NULL

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:

Finding incomplete orders: performance of LEFT JOIN compared to NOT IN

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.