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.