Wednesday, August 31, 2011

Netezza: Update a table's contents based on the values from other tables




Requirement:

field2 of Table1 needs to be updated with the values from Table2 and Table3 by matching key1.

Solution:

UPDATE Table1 T
SET field2 = COALESCE(P1.field2, P2.field2)
      FROM
            Table2 P1
            FULL OUTER JOIN
            Table3 P2
            ON
                  P1.key1 = P2.key1
WHERE
      T.key1 = COALESCE(P1.key1, P2.key1)
;