As in the SELECT query we can also use JOIN in UPDATE query. But there is difference in using join in SELECT query and in UPDATE query.
For eg:- (in SELECT query)
SELECT * FROM table1 AS T1
JOIN table2 AS T2 ON T2.t1 = T1.id
WHERE T2.a = 3;
As per this we will write the JOIN statement just before the WHERE statement in UPDATE query, but it is wrong. The JOIN statement statement must be near to the first table. That means the SET statement must be after the JOIN statement.
For eg:-
UPDATE table1 AS T1
JOIN table2 AS T2 ON T2.t1 = T1.id
SET T1.a = 2, T2.a = 2
WHERE T1.b = 3 AND T2.a = 4;
Note: This post is for my reference only.
For eg:- (in SELECT query)
SELECT * FROM table1 AS T1
JOIN table2 AS T2 ON T2.t1 = T1.id
WHERE T2.a = 3;
As per this we will write the JOIN statement just before the WHERE statement in UPDATE query, but it is wrong. The JOIN statement statement must be near to the first table. That means the SET statement must be after the JOIN statement.
For eg:-
UPDATE table1 AS T1
JOIN table2 AS T2 ON T2.t1 = T1.id
SET T1.a = 2, T2.a = 2
WHERE T1.b = 3 AND T2.a = 4;
Note: This post is for my reference only.