MERGE

Use the MERGE command to insert, update, or delete rows in a target table using data from a source such as a table, view, or subquery and based on rules specified for a matching condition.

Within the command, you must specify at least one matching_condition statement to identify the rows that you want to update, insert, or delete. For more information about the MERGE operations, or using MERGE during data loads, see the IBM® Netezza® Data Loading Guide.

Syntax

Syntax for using the MERGE command:

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 ]

The when_condition_then_action statement has the following options.

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

Inputs

The MERGE command takes the following inputs:

Table 1. MERGE Input
Input	Description
`target_table`	The name of the table where rows are to be inserted, updated, or deleted. The target table must reside in the current database (the database to which you are connected). The table must be a user table; MERGE is not supported for system catalog tables, external tables, versioned table, row-secure tables, nor a constant table.
`target_alias`	An optional alias name for the target table for use within the MERGE command.
`column_alias_list`	An optional list of alias names for the columns in the target table for use within the MERGE command.
`source_table`	The name of the source table that contains the rows used for the MERGE matching. The target table must reside in the current database (the database to which you are connected). The table must be a user table; MERGE is not supported for system catalog tables, external tables, versioned table, row-secure tables, nor a constant table.
`source_alias`	An optional alias name for the source table.
`column_alias_list`	An optional list of alias names for the columns in the source table.
`matching_condition`	The join condition used to identify the match columns for matching the rows in the source table to rows in the target table. The join condition should use unique keys so that there is at most one match of source table rows to the target table rows.
`when_condition_then_action`	At least one, and optionally multiple, match conditions and actions to take for the rows in the source and target tables. The conditions can take any of the following forms: Update rows `WHEN MATCHED [ AND bool_expression ] THEN UPDATE SET column = expression [, ...]` Delete rows `WHEN MATCHED [ AND bool_expression ] THEN DELETE` Insert rows `WHEN NOT MATCHED [ AND bool_expression ] THEN INSERT [ ( column_alias_list ) ] VALUES ( values_list )` Insert rows with default column values `WHEN NOT MATCHED [ AND bool_expression ] THEN INSERT DEFAULT VALUES` The command runs a matching sort of each row of the source and target table until a match is found.
ELSE IGNORE	This syntax is for DB2 compatibility and performs no action on the MERGE command in NPS.

Outputs

The MERGE command has the following outputs:

Table 2. MERGE outputs
Output	Description
`MERGE ins/upd/del`	The command was successful and inserted `ins` number of rows to the source table, updated `upd` number of rows in the source table, and deleted `del` number of rows from the source table.
`DBOS cannot modify a system catalog`	The MERGE command cannot update a system catalog table.
`ERROR: Cannot have UPDATE below WHEN NOT MATCHED`	You cannot specify an update or a delete operation in a WHEN NOT MATCHED condition. Similarly, you cannot specify an INSERT operation below a WHEN MATCHED condition.
`ERROR: Update cancelled: attempt to update a target row with values from multiple join rows`	The source table cannot have more than one row that matches a row in the target table based on the join condition in the ON clause. If this error occurs, you should check the source table and run queries to determine if it has multiple rows that match the join condition with the target table.

Privileges

You must be the admin user, the target table owner, the owner of the database or schema where the target table in defined, or your account must have the Insert, Update, and Delete privileges for the target table or the Table object class. You must also have the Select privilege for any table specified in a WHERE clause.

Usage

The following provides sample usage.

A merge of a new accounts table into an existing customers table where the match is on new unique customer key IDs:

MYDB.SCH1(USER)=> MERGE INTO customers as c using new_accounts as na 
on c.c_custkey = na.c_custkey when not matched then insert values (n.*);
MERGE 125/0/0

A merge of a new accounts table into an existing customers table where the match is on new unique customer key IDs:

MYDB.SCH1(USER)=> MERGE INTO customers as c using new_accounts as na 
on c.c_custkey = na.c_custkey when not matched then insert values (n.*);
MERGE 125/0/0

Troubleshooting MERGE failures due to multiple join rows

If the MERGE command returns ERROR: Update cancelled: attempt to update a target row with values from multiple join rows, a row in the target table matches more than one row in the merge source table. Check the source table for multiple rows that match the JOIN condition. As a simple example, assume that you have a table called inventory with a parts list:

TESTER.ADMIN(ADMIN)=> select * from inventory order by product_id;
 PRODUCT_ID |      PROD_DESC
------------+----------------------
        101 | red ballpoint pens
        102 | blue ballpoint pens
        103 | black ballpoint pens
        104 | scissors
(4 rows)

On a periodic basis, you merge the rows of a table called new_products that contains new parts available for order. A sample new_products table follows. Note that the table has two rows for parts with the same product ID (104), which already exists in the inventory table:

TESTER.ADMIN(ADMIN)=> select * from new_products order by productid;
 PRODUCTID |   PRODDESC
-----------+---------------
       104 | 8in scissors
       104 | 6in scissors
       105 | protractor
       106 | pencil eraser
(4 rows)

If you attempt to merge the new_products table into the inventory table and join on the product IDs, the MERGE command fails:

TESTER.ADMIN(ADMIN)=> merge into inventory as i using new_products as n 
on n.productid = i.product_id 
when not matched then 
     insert values (n.*) 
when matched then update set i.prod_desc = n.proddesc;
ERROR:  Update cancelled: attempt to update a target row with values from multiple join rows

If the MERGE command fails with this error, you should identify the duplicate rows in the source table that match the join conditions for the same row in the target table. For example:

TESTER.ADMIN(ADMIN)=> select productid from new_products n 
where n.productid in (select i.product_id from inventory i) 
group by n.productid having count(*) > 1;
 PRODUCTID
-----------
       104
(1 row)

You can then take steps to fix the source table to remove or address any of the multiple matching rows so that there is no more than one match using the join condition for a row in the target table.