我问了一个问题,得到了这个有帮助的答复。
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
JOIN
及其 ON
条件,就像在 SELECT
查询中一样。
UPDATE t1 JOIN t2 ON t1.id = t2.t1_id JOIN t3 ON t3.id = t2.t3_id SET t1.col = 'newval'
答案是肯定的,你可以。
试试这样:
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]
achieving the same result 的另一种方法是根本不使用 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
UPDATE TABLE_A, TABLE_B SET TABLE_A.column_c = TABLE_A.column_c +1 WHERE TABLE_A.join_col = TABLE_B.join_col
SELECT * FROM TABLE_A, TABLE_B ...
相同的方式执行隐式 JOIN
UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... [WHERE where_condition]
后来,手册说明:“table_references
子句列出了连接中涉及的表。它的语法在 Section 13.2.9.2, JOIN Syntax 中描述。”
下面是包含 JOIN
和 WHERE
的 update 查询。同样,我们可以使用多个 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')
另一种总体计划:
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;
是的,您可以为更新语句执行三表连接。这是一个例子:
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";
对于 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;
没有一个答案对我不起作用我在 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 data} JOIN table C ON {join data} JOIN {more join tables} SET A.column = {expression}
(如果这个该死的编辑不让我在没有完整发布的情况下输入换行符,请原谅我)