MERGE v16

Suggest edits

Name

MERGE — conditionally insert, update, or delete 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 ]
   [ DELETE 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, UPDATE, or DELETE rows, a task which would otherwise require multiple procedural language statements.

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.

Specify the DELETE where_condition to clean up data in a table while populating or updating it. The only rows affected by this clause are those rows in the target table that are updated by the merge operation. The DELETE WHERE condition evaluates the updated value, not the original value that was evaluated by the UPDATE SET ... WHERE condition. If a row of the target table meets the DELETE condition but is not included in the join defined by the ON clause, then it is not deleted.

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 UPDATE, INSERT, and conditionally DELETE rows on the target table using MERGE statement:

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

This example shows how to UPDATE, INSERT, and DELETE rows on the target table conditionally 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
    DELETE WHERE sid = 1
WHEN NOT MATCHED THEN
    INSERT VALUES (s.sid, s.delta) WHERE s.sid >= 5;
← Prev

INSERT

↑ Up

Compatible SQL commands

Next →

LOCK