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:
