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
JOIN and its ON condition, same as you would in a SELECT query.
UPDATE t1 JOIN t2 ON t1.id = t2.t1_id JOIN t3 ON t3.id = t2.t3_id SET t1.col = 'newval'
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]
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
UPDATE TABLE_A, TABLE_B SET TABLE_A.column_c = TABLE_A.column_c +1 WHERE TABLE_A.join_col = TABLE_B.join_col
JOIN in the same way doing SELECT * FROM TABLE_A, TABLE_B ... does
UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... [WHERE where_condition] Later on, the manual states: "The table_references clause lists the tables involved in the join. Its syntax is described in Section 13.2.9.2, JOIN Syntax."
Below is the update query which includes both JOIN and WHERE. In the same way, we can use multiple join/where clauses:
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')
An alternative general plan:
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;
Yes, you can do a thre-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";
For a 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;
none of answer does not work for me I find this on mysql manual
UPDATE T1,T2 INNER JOIN T2 ON T1.C1 = T2.C1 SET T1.C2 = T2.C2, T2.C3 = expr WHERE condition
Follow WeChat
Success story sharing
UPDATE table A JOIN table B ON {join data} JOIN table C ON {join data} JOIN {more join tables} SET A.column = {expression}(forgive me if this blasted editor won't let me enter newlines without doing a full post)