How to Update Multiple MySQL Tables Using a LEFT JOIN?

Updating Multiple Tables via LEFT JOIN in MySQL

Question:

How can you update fields in a table using a LEFT JOIN across multiple tables? Consider a scenario where you want to update all rows in the result set from the following LEFT JOIN query:

SELECT T1.* FROM T1 LEFT JOIN T2 ON T1.id = T2.id WHERE T2.id IS NULL

Answer:

MySQL allows you to perform UPDATE statements using any join type supported in SELECT statements, including LEFT JOIN. The proper syntax is as follows:

UPDATE  t1
LEFT JOIN
        t2
ON      t2.id = t1.id
SET     t1.col1 = newvalue
WHERE   t2.id IS NULL

Note that for better performance in a SELECT statement, consider using NOT IN / NOT EXISTS syntax:

SELECT  t1.*
FROM    t1
WHERE   t1.id NOT IN
        (
        SELECT  id
        FROM    t2
        )

For performance details, refer to this article: Finding incomplete orders: performance of LEFT JOIN compared to NOT IN.

However, MySQL does not support using the target table in a subquery within an UPDATE statement. Thus, you must rely on the less efficient LEFT JOIN syntax for updates involving multiple tables.

The above is the detailed content of How to Update Multiple MySQL Tables Using a LEFT JOIN?. For more information, please follow other related articles on the PHP Chinese website!