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?

Answer 1

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.

Answer 2

Hi,

Quassnoi code is really helpful and provided in a good understandable format,

I made this example code to help you based on Quassnoi's answer:

UPDATE orders

left join invoices on orders.id = invoices.oid left join products_sold on orders.id = products_sold.oid left join products on products_sold.pid = products.id

SET orders.id = 2, invoices.oid=2,products_sold.oid=2 WHERE orders.id = '27'

Note: this will only update the first entry in the joined table matching the given criteria.

Regards, Hyder A PHP/MySQL coder for www.HitBiz.Net International