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);