Netezza MERGE command to Manipulate Records from Table

Use the Netezza MERGE command to insert, update, or delete rows in a target table using data from a source such as a table, view, or sub-query and based on rules specified for a matching condition in the merge statements. In the Netezza merge command, you must specify at least one matching_condition statement to identify the rows that you want to update, insert or delete.

This feature is not supported in all versions of Netezza. IBM has introduced this feature in Netezza 7.2.1 or higher. MEGRE command is used to handle incremental loading.

You can use Merge command to implement slowly changing dimension (SCD) type 2.

Netezza MERGE command Syntax

Below is the syntax for MERGE statement that Netezza uses:

MERGE INTO target_table [ [ AS ] target_alias [ ( column_alias_list ) ] ]
USING source_table [ [ AS ] source_alias [ ( column_alias_list ) ] ]
ON matching_condition 
when_condition_then_action [, ...]
[ ELSE IGNORE ]

Where when_condition_then_action can be:

WHEN MATCHED [ AND bool_expression ] THEN
UPDATE SET column = expression [, ...]
| WHEN MATCHED [ AND bool_expression ] THEN
DELETE
| WHEN NOT MATCHED [ AND bool_expression ] THEN
INSERT [ ( column_alias_list ) ] VALUES ( values_list )
|WHEN NOT MATCHED [ AND bool_expression ] THEN
INSERT DEFAULT VALUES

Netezza MERGE command Example

Below is the sample MERGE statement implementation in Netezza:

--This table holds the records
CREATE TABLE merge_demo1
(
ID INT NOT NULL,
FirstName NVARCHAR(100),
LastName NVARCHAR(100)
);

--This table holds intermediate table
CREATE TABLE merge_demo2
(
ID INT NOT NULL,
FirstName NVARCHAR(100),
LastName NVARCHAR(100)
);

--Sample records inserted to table
INSERT INTO merge_demo1 VALUES (1, 'aaaa', 'bbbb');
INSERT INTO merge_demo1 VALUES (2, 'cccc', 'dddd');
INSERT INTO merge_demo1 VALUES (3, 'eeee', 'ffff');
INSERT INTO merge_demo1 VALUES (4, 'gggg', 'hhhh');
INSERT INTO merge_demo1 VALUES (5, 'iiii', 'jjjj');

--Let us say there is last name change for 'cccc' and 'eeee'
INSERT INTO merge_demo2 VALUES (2, 'cccc', 'kkkk');
INSERT INTO merge_demo2 VALUES (3, 'eeee', 'llll');

-- MERGE Command
MERGE INTO merge_demo1 AS A
using merge_demo2 AS B
ON A.ID = B.ID
WHEN MATCHED THEN
UPDATE SET A.LastName = B.LastName
WHEN NOT MATCHED THEN
INSERT VALUES (B.ID, B.FirstName, B.LastName);