在netezza中使用左联接进行更新 [英] Update using left join in netezza

问题描述

在更新期间，我需要在netezza中执行两个表的左连接.我怎样才能做到这一点?左联接与三个表一起工作，但不适用于两个表.

I need to perform a left join of two tables in netezza during an update. How can i achieve this ? Left join with three tables are working but not with two tables.

UPDATE table_1
SET c2 = t2.c2
FROM
    table_1 t1
    LEFT JOIN table_2.t1
    ON t1.c1=t2.c1
    LEFT JOIN table_3 t3
    ON t2.c1=t3.c1

这有效，但

UPDATE table_1
SET c2 = t2.c2
FROM table_1 t1
    LEFT JOIN table_2.t1
    ON t1.c1=t2.c1

这就像尝试更新多个列.

this says like trying to update multiple columns.

谢谢， 马尼拉西那.

推荐答案

在Netezza中通过联接执行UPDATE TABLE时，重要的是要了解要更新的表总是 隐式INNER JOINed with FROM列表.此行为是在此处记录的.

When performing an UPDATE TABLE with a join in Netezza, it's important to understand that the table being updated is always implicitly INNER JOINed with the FROM list. This behavior is documented here.

您的代码实际上是将table_1连接到其自身(一个没有别名的副本，一个以t1作为别名的副本).由于在table_1的两个版本之间没有联接条件，因此您将获得交叉联接，该联接提供了多个试图更新table_1的行.

Your code is actually joining table_1 to itself (one copy with no alias, and one with t1 as an alias). Since there is no join criteria between those two versions of table_1, you are getting a cross join which is providing multiple rows that are trying to update table_1.

使用OUTER连接处理UPDATE的最好方法是采用这样的子选择:

The best way to tackle an UPDATE with an OUTER join is to employ a subselect like this:

TESTDB.ADMIN(ADMIN)=> select * from table_1 order by c1;
 C1 | C2
----+----
  1 |  1
  2 |  2
  3 |  3
(3 rows)

TESTDB.ADMIN(ADMIN)=> select * from table_2 order by c1;
 C1 | C2
----+----
  1 | 10
  3 | 30
(2 rows)


TESTDB.ADMIN(ADMIN)=> UPDATE table_1 t1
SET t1.c2 = foo.c2
FROM (
      SELECT t1a.c1,
         t2.c2
      FROM table_1 t1a
         LEFT JOIN table_2 t2
         ON t1a.c1 = t2.c1
   )
   foo
WHERE t1.c1 = foo.c1;
UPDATE 3

TESTDB.ADMIN(ADMIN)=> select * from table_1 order by c1;
 C1 | C2
----+----
  1 | 10
  2 |
  3 | 30
(3 rows)

这篇关于在netezza中使用左联接进行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！