我问了一个问题，得到了这个有用的回答。
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
现在，如果有三个表涉及到这样的东西，我希望这样做。
UPDATE tableC c JOIN tableB b JOIN tableA a
我的问题基本上是…有可能在一个UPDATE语句上做三个表连接吗?它的正确语法是什么?
我要做以下事情吗?
JOIN tableB, tableA
JOIN tableB JOIN tableA
当前回答
备选的总体规划:
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}
例子:
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;
其他回答
没有一个答案不适合我，我在mysql手册上找到了这个
UPDATE T1,T2 INNER JOIN T2 ON T1.C1 = T2.C1 SET T1.C2 = T2.C2, T2.C3 = expr WHERE condition
备选的总体规划:
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}
例子:
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;
PostgreSQL的例子:
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;
答案是肯定的，你可以。
试着这样做:
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
对于一般的更新连接:
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
