How to do 3 table JOIN in UPDATE query?

2018-07-03 20:26:42

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

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]

实现相同结果的另一种方法是根本不使用JOIN关键字。

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

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')

链接地址: http://www.djcxy.com/p/94298.html

上一篇: SQL从一个表web2py中左连接两次

下一篇: 如何在UPDATE查询中执行3个表JOIN？