UPDATE multiple tables in MySQL using LEFT JOIN

by 
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:

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.

More Related Contents:

  1. MySQL – UPDATE query based on SELECT Query
  2. MySQL, update multiple tables with one query
  3. MySQL update table based on another tables value
  4. UPDATE multiple rows with different values in one query in MySQL
  5. Multiple Updates in MySQL
  6. mysql update column with value from another table
  7. MySQL error code: 1175 during UPDATE in MySQL Workbench
  8. MySQL – UPDATE query with LIMIT
  9. mysql after insert trigger which updates another table’s column
  10. update multiple rows using limit in mysql?
  11. How to prepend a string to a column value in MySQL?
  12. MySQL update a joined table
  13. MySQL/SQL: Update with correlated subquery from the updated table itself
  14. MySql update two tables at once
  15. MySQL: update a field only if condition is met
  16. Advanced MySql Query: Update table with info from another table
  17. What are differences between INSERT and UPDATE in MySQL?
  18. Table is ‘read only’
  19. MySql – Way to update portion of a string?
  20. MySQL Error: Incorrect usage of UPDATE and LIMIT
  21. Does MySQL overwrite a column of same value on update?
  22. MySQL UPDATE syntax with multiple tables using WHERE clause
  23. mySQL UPDATE query returns “0 rows affected”
  24. MySQL UPDATE and SELECT in one pass
  25. How to create and insert a JSON object using MySQL queries?
  26. The best way to remove value from SET field?
  27. MySQL: How do I use delimiters in triggers?
  28. Update multiple rows with multiple ‘where’ clauses for each individual row
  29. MySQL behavior of ON DUPLICATE KEY UPDATE for multiple UNIQUE fields
  30. MySQL update statement to store ranking positions

Leave a Comment