MySQL 如何在 UPDATE 查询中执行 3 个表 JOIN？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15209414/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to do 3 table JOIN in UPDATE query?
提问by Ricky
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.
如果有 3 个表涉及这样的事情，现在我正在寻找这样做。
UPDATE tableC c JOIN tableB b JOIN tableA a
my question is basically... is this possible to do 3 table join on an UPDATEstatement? and what is the correct syntax for it? Thank you. Do i do the...
我的问题基本上是......这可以在一个UPDATE语句上进行 3 个表连接吗？它的正确语法是什么？谢谢你。我做...
JOIN tableB, tableA
JOIN tableB JOIN tableA
回答by echo_Me
the answer is yesyou can
答案是yes你可以
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]
回答by Matas Vaitkevicius
Alternative way of achieving same resultis not to use JOINkeyword at all.
实现相同结果的替代方法是根本不使用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
回答by Nitin Shukla
Below is the Update query which includes JOIN& WHEREboth. Same way we can use multiple join/where clause, Hope it will help you :-
下面是包含JOIN&WHERE两者的更新查询。同样的方式我们可以使用多个 join/where 子句，希望它会帮助你：-
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')
回答by UncaAlby
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;
回答by Mс1er
For PostgreSQL example:
对于 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;
