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
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
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;
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";
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;
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')
Source: Stackoverflow.com