Netezza: MERGE SQL statement

Netezza NPS release 7.2.1 comes with new MERGE SQL statement. Here are some usage examples.

• To build tables for test environment follow the link. 

-----------------------------------------------

-----  Using heap table ----

-----------------------------------------------

MERGE INTO my_target_table a

     USING my_source_table b

ON   a.UID   =  b.UID

WHEN MATCHED  AND a.DATAFIELD <> b.DATAFIELD THEN

       UPDATE SET a.DATAFIELD = b.DATAFIELD, a.UPDATED = CURRENT_TIMESTAMP

WHEN NOT MATCHED  THEN

       INSERT VALUES (b.UID,B.DATAFIELD,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP)

;

-----------------------------------------------

-----  Using TET (transient external table) ---

-----------------------------------------------

MERGE INTO my_target_table A

     USING (SELECT * FROM external '/tmp/data.csv'

                                  ( UID       INT4

                                  , DATAFIELD CHARACTER VARYING(64))

                     using (Delim ',' SkipRows 0 MaxErrors 300 Logdir '/tmp'  )) B

ON   a.UID   =  b.UID

WHEN MATCHED  AND a.DATAFIELD <> b.DATAFIELD THEN

       UPDATE SET a.DATAFIELD = b.DATAFIELD ,a.UPDATED = CURRENT_TIMESTAMP

WHEN NOT MATCHED  THEN

       INSERT VALUES (b.UID,B.DATAFIELD,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP)

;

-----------------------------------------------

-----  Using intermediate TEMP table ---

-----------------------------------------------

create temp table TEMP_source_table

as select * from external '/tmp/data.csv' (UID INT4, DATAFIELD CHARACTER VARYING(64)) 

   using (Delim ',' SkipRows 0 MaxErrors 300 Logdir '/tmp')

;

MERGE INTO my_target_table   A

     USING TEMP_source_table B

ON   a.UID   =  b.UID

WHEN MATCHED AND a.DATAFIELD <> b.DATAFIELD THEN

       UPDATE SET   a.DATAFIELD = b.DATAFIELD,a.UPDATED = CURRENT_TIMESTAMP

WHEN NOT MATCHED  THEN

       INSERT VALUES (b.UID,B.DATAFIELD,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP)

;

-----------------------------------------------

Notice, Aginity Workbench 4.8.0 report only INSERTED records statistics, but UPDATED.  The nzsql reports both UPDATED and INSERTED records affected. 

REFER: 

• New features and changes in Release 7.2.1 - IBM

• MERGE - IBM