Friday, February 10, 2012

MYSQL: JOIN in UPDATE query

       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.