How to do 3 table JOIN in UPDATE query?

484
votes

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 3 tables involved something like this.

    UPDATE tableC c JOIN tableB b JOIN tableA a

my question is basically... is this possible to do 3 table join on an UPDATE statement? and what is the correct syntax for it? Thank you. Do i do the... 

 JOIN tableB, tableA
 JOIN tableB JOIN tableA
6
mysqljoin
Sure it is possible. Give it a try. The syntax is just like you have it -you just need to add the next JOIN and its ON condition, same as you would in a SELECT query. - Michael Berkowski
UPDATE t1 JOIN t2 ON t1.id = t2.t1_id JOIN t3 ON t3.id = t2.t3_id SET t1.col = 'newval' - Michael Berkowski
The mentioned question is here: stackoverflow.com/questions/15206746/… - Urs

6 Answers

844
votes

the answer is yes you can

try it like that

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

EDIT:

For general Update join :

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

Alternative way of achieving same result is not to use 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
12
votes

Below is the Update query which includes JOIN & WHERE both. Same way we can use multiple join/where clause, Hope it will help you :- 

UPDATE opportunities_cstm oc JOIN opportunities o ON oc.id_c = o.id
 SET oc.forecast_stage_c = 'APX'
 WHERE o.deleted = 0
   AND o.sales_stage IN('ABC','PQR','XYZ')
2
votes

An alternative General Plan, which I'm only adding as an independent Answer because the blasted "comment on an answer" won't take newlines without posting the entire edit, even though it isn't finished yet.

UPDATE table A
JOIN table B ON {join fields}
JOIN table C ON {join fields}
JOIN {as many tables as you need}
SET A.column = {expression}

Example:

UPDATE person P
JOIN address A ON P.home_address_id = A.id
JOIN city C ON A.city_id = C.id
SET P.home_zip = C.zipcode;
1
votes

For PostgreSQL example:

UPDATE TableA AS a
SET param_from_table_a=FALSE -- param FROM TableA
FROM TableB AS b
WHERE b.id=a.param_id AND a.amount <> 0;
1
votes

Yes, you can do a 3 table join for an update statement. Here is an example :

    UPDATE customer_table c 

      JOIN  
          employee_table e
          ON c.city_id = e.city_id  
      JOIN 
          anyother_ table a
          ON a.someID = e.someID

    SET c.active = "Yes"

    WHERE c.city = "New york";