MERGE v15

Suggest edits

Name

MERGE — conditionally insert or update rows of a table.

Synopsis

MERGE INTO target_table_name [ target_alias ]
USING data_source [ source_alias ] ON ( join_condition )
merge_update_clause merge_insert_clause

-- where merge_update_clause is
WHEN MATCHED THEN 
   UPDATE SET column = value, ... [ WHERE where_condition ]

-- and merge_insert_clause is
WHEN NOT MATCHED THEN 
   INSERT [(col_list)] VALUES (val_list) [ WHERE where_condition ]

Description

MERGE allows you to select rows from one or more sources for update or insertion into a table. You specify the join condition to determine whether to update or insert into the target table. You specify conditional UPDATE and INSERT statements using the WHERE clause in the MERGE statement.

MERGE provides a single SQL statement that can conditionally INSERT or UPDATE rows, a task which would otherwise require multiple procedural language statements.

:

In version 15, EDB Postgres Advanced Server provides only partial support for Oracle-compatible MERGE syntax.!!!

For more information, see MERGE statement.

Parameters

target_table_name

The name (optionally schema-qualified) of the target table to merge into.

target_alias

A substitute name for the target table. When an alias is provided, it completely hides the actual name of the table.

data_source

The name (optionally schema-qualified) of the source table or a source query. The source table is the source table name, view name, or transition table name. The source query (SELECT or VALUES statement) supplies the rows to be merged into the target_table_name.

source_alias

A substitute name for the data source. When an alias is provided, it completely hides the actual name of the table.

join_condition

An expression resulting in a value of type boolean similar to a WHERE clause that specifies which rows in the data_source match rows in the target_table_name.

merge_update_clause

Specifies the new column values of the target table. It performs this update if the join_condition of the ON clause is true.

Specify the where_condition if you want the database to execute the update operation only if the specified condition is true. The condition can refer to either the data source or the target table. If the condition is not true, then the database skips the update operation when merging the row into the table.

You can specify this clause by itself or with the merge_insert_clause. If you specify both, then they can be in either order.

merge_insert_clause

The merge_insert_clause specifies values to insert into the column of the target table if the condition of the ON clause is false. If you omit the column list after the INSERT keyword, then the number of columns in the target table must match the number of values in the VALUES clause.

To insert all of the source rows into the table, you can use a constant filter predicate in the ON clause condition. An example of a constant filter predicate is ON (0=1). It recognizes such a predicate and makes an unconditional insert of all source rows into the table. This approach is different from omitting the merge_update_clause. In that case, the database still must perform a join. With constant filter predicate, no join is performed.

Specify the where_clause if you want the database to execute the insert operation only if the specified condition is true. The condition can refer only to the data source table. Database skips the insert operation for all rows for which the condition is not true.

You can specify this clause by itself or with the merge_update_clause. If you specify both, then they can be in either order.

Examples

Create tables target and source:

CREATE TABLE target (tid integer, balance integer);
CREATE TABLE source (sid integer, delta integer);

Add rows to both the tables:

# Insert rows into target table
INSERT INTO target VALUES (1, 0);
INSERT INTO target VALUES (2, 20);
INSERT INTO target VALUES (3, 0);

# Insert rows into source table
INSERT INTO source VALUES (1, 100);
INSERT INTO source VALUES (2, 200);
INSERT INTO source VALUES (3, 300);
INSERT INTO source VALUES (4, 100);
INSERT INTO source VALUES (5, 300);
INSERT INTO source VALUES (6, 600);

This example shows how to UPDATE and INSERT rows on the target table using the MERGE statement:

MERGE INTO target t
USING source s
ON (t.tid = s.sid)
WHEN MATCHED THEN
    UPDATE SET balance = s.delta
WHEN NOT MATCHED THEN
    INSERT VALUES (s.sid, s.delta);

This example shows how to conditionally UPDATE and INSERT rows on the target table using the MERGE statement:

MERGE INTO target t
USING source s
ON (t.tid = s.sid)
WHEN MATCHED THEN
    UPDATE SET balance = s.delta WHERE balance = 0
WHEN NOT MATCHED THEN
    INSERT VALUES (s.sid, s.delta) WHERE s.sid >= 5;
← Prev

INSERT

Next →

LOCK