How can I do three table JOINs in an UPDATE query?

Question

I asked a question and got this reply which helped.

   UPDATE TABLE_A a JOIN TABLE_B b    ON a.join_col = b.join_col AND a.column_a = b.column_b    SET a.column_c = a.column_c + 1 

Now I am looking to do this if there are three tables involved something like this.

    UPDATE tableC c JOIN tableB b JOIN tableA a 

My question is basically... is it possible to do three table joins on an UPDATE statement? And what is the correct syntax for it?

Do I do the following?

 JOIN tableB, tableA  JOIN tableB JOIN tableA 

Answer 1

The answer is yes, you can.

Try it like this:

UPDATE TABLE_A a     JOIN TABLE_B b ON a.join_col = b.join_col AND a.column_a = b.column_b     JOIN TABLE_C c ON [condition] SET a.column_c = a.column_c + 1 

For a general update join:

UPDATE TABLEA a JOIN TABLEB b ON a.join_colA = b.join_colB SET a.columnToUpdate = [something] 

Answer 2

An alternative way of achieving the same result is not to use the JOIN keyword at all.

UPDATE TABLE_A, TABLE_B SET TABLE_A.column_c = TABLE_B.column_c + 1 WHERE TABLE_A.join_col = TABLE_B.join_col