mysql update with join condition code example

Example 1: update left join mysql

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

Example 2: MySQL UPDATE JOIN

You often use joins to query rows from a table that have (in the case of INNER JOIN) or may not have (in the case of LEFT JOIN) matching rows in another table. In MySQL, you can use the JOIN clauses in the UPDATE statement to perform the cross-table update.

The syntax of the MySQL UPDATE JOIN  is as follows:

UPDATE T1, T2,
[INNER JOIN | LEFT JOIN] T1 ON T1.C1 = T2. C1
SET T1.C2 = T2.C2, 
    T2.C3 = expr
WHERE condition
Let’s examine the MySQL UPDATE JOIN  syntax in greater detail:

First, specify the main table ( T1 ) and the table that you want the main table to join to ( T2 ) after the UPDATE clause. Notice that you must specify at least one table after the UPDATE  clause. The data in the table that is not specified after the UPDATE  clause will not be updated.
Next, specify a kind of join you want to use i.e., either INNER JOIN  or LEFT JOIN  and a join predicate. The JOIN clause must appear right after the UPDATE clause.
Then, assign new values to the columns in T1 and/or T2 tables that you want to update.
After that, specify a condition in the WHERE clause to limit rows to rows for updating.