Update using left join in netezza

Question

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

this works but

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.

Thanks, Manirathinam.

Answer 1

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.

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.

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)