UPDATE multiple tables in MySQL using LEFT JOIN

Question

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?

Accepted Answer

```
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.

UPDATE multiple tables in MySQL using LEFT JOIN

Solution

Problem

Related problems