MERGE INTO

Function

MERGE INTO matches data in a source table with that in a target table based on a join condition. If data matches, UPDATE will be executed on the target table. Otherwise, INSERT will be executed. MERGE INTO handles both UPDATE and INSERT operations within a single statement, potentially improving performance.

Precautions

To run MERGE INTO, you must have the UPDATE and INSERT permissions for the target table, as well as the SELECT permission for the source table.
PREPARE is not supported.
MERGE INTO cannot be executed during redistribution.
MERGE INTO cannot be executed for target tables that contain triggers.
When executing MERGE INTO for the round-robin table, you are advised to disable the GUC parameter allow_concurrent_tuple_update, or some MERGE INTO statements are not supported.

Syntax

     
          MERGE [/*+ plan_hint */] INTO table_name [ partition_clause ] [ [ AS ] alias ]
USING { { table_name | view_name } | subquery } [ partition_clause ] [ [ AS ] alias ]
ON ( condition )
[
  WHEN MATCHED THEN
  UPDATE SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
  [ WHERE condition ]
]
[
  WHEN NOT MATCHED THEN
  INSERT { DEFAULT VALUES |
  [ ( column_name [, ...] ) ] VALUES ( { expression | DEFAULT } [, ...] ) [, ...] [ WHERE condition ] }
];

where partition_clause can be:
PARTITION ( partition_name ) | PARTITION FOR ( partition_key_value [, ...] )

Parameter Description

**Table 1** MERGE INTO parameters
Parameter	Description	Value Range
plan_hint clause	Following the keyword in the */+ /* format, hints are used to optimize the plan generated by a specified statement block. For details, see "DWS Performance Tuning" > "SQL Tuning" > "Hint-based Tuning" in Data Warehouse Service (DWS) Developer Guide.	-
INTO clause	Specifies the target table that is being updated or has data being inserted. table_name Specifies the name of the target table. partition_clause Specifies a partition in the target table. This parameter is supported only by clusters of version 9.1.0 or later. Partitions can be specified only if the target table is a partitioned one. The specified partition name must already exist in the target table or the partition specified by the partition key already exists. alias Specifies the alias for the target table.	Valid table name.
USING clause	Specifies the source table, which can be a table, view, or subquery. partition_clause Specifies a partition in the source table. This parameter is supported only by clusters of version 9.1.0 or later. Partitions can be specified only if the source table is a partitioned one. Partitions cannot be specified for views or subqueries. The specified partition name must already exist in the source table or the partition specified by the partition key already exists. alias Specifies the alias for the target table.	Valid source table name.
ON clause	Specifies the condition used to match data between the source and target tables. Columns in the condition cannot be updated. The ON association condition can be ctid, xc_node_id, or tableoid.	-
WHEN MATCHED clause	Performs UPDATE if data in the source table matches that in the target table based on the condition. Distribution keys cannot be updated. System catalogs and system columns cannot be updated. With a partition specified in the target table, if the UPDATE causes the partition keys to change: If the target table is a column-store table, the error message "The inserted partition key is not mapped to the specified 'partition_name' partition" is displayed. If the target table is a row-store table: If ENABLE ROW MOVEMENT is set for the target table, no error will be reported, the updated data will belong to the new partition. If Disable ROW MOVEMENT is set for the target table, the error message "fail to update partitioned table "table_name" will be displayed.	-
WHEN NOT MATCHED clause	Specifies that the INSERT operation is performed if data in the source table does not match that in the target table based on the condition. An INSERT clause cannot contain multiple VALUES. The order of WHEN MATCHED and WHEN NOT MATCHED clauses can be reversed. One of them can be used by default, but they cannot be both used at one time. Two WHEN MATCHED or WHEN NOT MATCHED clauses cannot be specified at the same time. If a partition in the target table is specified and more data beyond the scope of the partition is inserted, the error message "The inserted partition key is not mapped to the specified 'partition_name' partition" will be displayed.	-
DEFAULT	Sets the column to its default value.	The value is NULL if no specified default value has been assigned to it.
WHERE condition	Specifies the conditions for the UPDATE and INSERT clauses. The two clauses will be executed only when the conditions are met. This parameter can be left empty. System columns cannot be referenced in WHERE condition.	-

Examples

Create the target table products and source table newproducts, and insert data to them.

     
      
        
        
          CREATE TABLE products
(
product_id INTEGER,
product_name VARCHAR2(60),
category VARCHAR2(60)
);

INSERT INTO products VALUES (1501, 'vivitar 35mm', 'electrncs');
INSERT INTO products VALUES (1502, 'olympus is50', 'electrncs');
INSERT INTO products VALUES (1600, 'play gym', 'toys');
INSERT INTO products VALUES (1601, 'lamaze', 'toys');
INSERT INTO products VALUES (1666, 'harry potter', 'dvd');

CREATE TABLE newproducts
(
product_id INTEGER,
product_name VARCHAR2(60),
category VARCHAR2(60)
);

INSERT INTO newproducts VALUES (1502, 'olympus camera', 'electrncs');
INSERT INTO newproducts VALUES (1601, 'lamaze', 'toys');
INSERT INTO newproducts VALUES (1666, 'harry potter', 'toys');
INSERT INTO newproducts VALUES (1700, 'wait interface', 'books');

         

       

     
    

Run MERGE INTO. If the product_id field is matched and the product_name field in the target table is not play gym, UPDATE is executed. If the product_id field is not matched and the category field in the source table is books, INSERT is executed.

     
          MERGE INTO products p   
USING newproducts np   
ON (p.product_id = np.product_id)   
WHEN MATCHED THEN  
  UPDATE SET p.product_name = np.product_name, p.category = np.category WHERE p.product_name != 'play gym'  
WHEN NOT MATCHED THEN  
  INSERT VALUES (np.product_id, np.product_name, np.category) WHERE np.category = 'books';

View the query result. The data in product_id of rows 1502 and 1666 in the products table was updated, and the data in row 1700 was newly inserted.

     
          SELECT * FROM products ORDER BY product_id;
 product_id |  product_name  | category  
------------+----------------+-----------
       1501 | vivitar 35mm   | electrncs
       1502 | olympus camera | electrncs
       1600 | play gym       | toys
       1601 | lamaze         | toys
       1666 | harry potter   | toys
       1700 | wait interface | books
(6 rows)