MERGE INTO

Function

The MERGE INTO statement is used to conditionally match data in a target table with that in a source table. If data matches, UPDATE is executed on the target table; if data does not match, INSERT is executed. You can use this syntax to run UPDATE and INSERT at a time for convenience.

Precautions

In concurrent MERGE INTO scenarios, the update operations triggered on the same CU will cause lock conflicts in traditional column-store tables and result in low performance. For HStore tables, the operations can be concurrently performed, and the MERGE INTO performance can be more than 100 times that of column-store tables.

Syntax

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

MERGE INTO table_name [ [ AS ] alias ]
USING { { table_name | view_name } | subquery } [ [ 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 ] }
];

Parameters

• INTO clause

  Specifies the target table that is being updated or has data being inserted.

  • table_name

    Specifies the name of the target table.

  • alias

    Specifies the alias for the target table.

    Value range: a string. It must comply with the naming convention.

• USING clause

  Specifies the source table, which can be a table, view, or subquery.

• 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 columns, system catalogs, and system columns cannot be updated.

• 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 can contain only one VALUES.
  • The sequence of WHEN NOT MATCHED and WHEN NOT MATCHED clauses can be exchanged. One of them can be omitted, but they cannot be omitted at the same time.
  • Two WHEN MATCHED or WHEN NOT MATCHED clauses cannot be specified at the same time.

Example

1
2

CREATE TABLE target(a int, b int)WITH(ORIENTATION = COLUMN, ENABLE_HSTORE = ON);
INSERT INTO target VALUES(1, 1),(2, 2);

1
2

CREATE TABLE source(a int, b int)WITH(ORIENTATION = COLUMN, ENABLE_HSTORE = ON);
INSERT INTO source VALUES(1, 1),(2, 2),(3, 3),(4, 4),(5, 5);

1
2
3
4
5
6
7

MERGE INTO target t   
USING source s   
ON (t.a = s.a)   
WHEN MATCHED THEN  
  UPDATE SET t.b = t.b + 1  
WHEN NOT MATCHED THEN  
  INSERT VALUES (s.a, s.b) WHERE s.b % 2 = 0;