MERGE

The MERGE statement allows you to select rows from one or more sources for update or insertion into a target table. You can specify conditions that are used to evaluate what rows are updated or inserted into the target table.

Use this statement to combine multiple INSERT and UPDATE statements.

MERGE is a deterministic statement: You cannot update the same row of the target table multiple times in the same MERGE statement.

Required privilege

No privilege is required for the owner of the target table and the source table.

INSERT or UPDATE on a target table owned by another user and SELECT on a source table owned by another user.

SQL syntax

MERGE INTO [Owner.]TargetTableName [Alias] USING
    {[Owner.]SourceTableName|(Subquery)}[Alias] ON (Condtion)
    {MergeUpdateClause MergeInsertClause |
     MergeInsertClause MergeUpdateClause |
     MergeUpdateClause | MergeInsertClause
    }

The syntax for MergeUpdateClause:

WHEN MATCHED THEN UPDATE SET SetClause [WHERE Condition1]

The syntax for MergeInsertClause:

WHEN NOT MATCHED THEN INSERT [Columns [,...]] VALUES
    ( {{Expression | DEFAULT|NULL} [,...] }) [WHERE Condition2]

Parameters

The MERGE statement has the parameters:

Description

• You can specify the MergeUpdateClause by itself or with the MergeInsertClause. Alternatively, you can specify the MergeInsertClause by itself or with the MergeUpdateClause. If you specify both, you can specify them in either order.

• If DUAL is the only table specified in the USING clause and it is not referenced elsewhere in the MERGE statement, specify DUAL as a simple table rather than use it in a subquery. In this simple case, to help performance, specify a key condition on a unique index of the target table in the ON clause.

• Restrictions on the MergeUpdateClause:

  • You cannot update a column that is referenced in the ON condition clause.

  • You cannot update source table columns.

• Restrictions on the MergeInsertClause:

  • You cannot insert values of target table columns.

• Other restrictions:

  • Do not use the set operators in the subquery of the source table.

  • Do not use a subquery in the WHERE condition of either the MergeUpdateClause or the MergeInsertClause.

  • The target table cannot be a detail table of a materialized view.

  • The RETURNING clause cannot be used in a MERGE statement.

Examples

In this example, dual is specified as a simple table. There is a key condition on the UNIQUE index of the target table specified in the ON clause. The DuplicateBindMode attribute is set to 1 in this example. (The default is 0.)

Command> CREATE TABLE mergedualex (col1 TT_INTEGER NOT NULL, 
         col2 TT_INTEGER, PRIMARY KEY (col1));
Command> MERGE INTO mergedualex USING dual ON (col1 = :v1)
>        WHEN MATCHED THEN UPDATE SET col2 = col2 + 1
>        WHEN NOT MATCHED THEN INSERT VALUES (:v1, 1);

Type '?' for help on entering parameter values.Type '*' to end prompting and abort the command.Type '-' to leave the parameter unbound.Type '/;' to leave the remaining parameters unbound and execute the command.

Enter Parameter 1  'V1' (TT_INTEGER) > 10
1 row merged.
Command> SELECT * FROM mergedualex;
< 10, 1 >
1 row found.

In this example, a table called contacts is created with columns employee_id and manager_id. One row is inserted into the contacts table with values 101 and NULL for employee_id and manager_id respectively. The MERGE statement is used to insert rows into the contacts table using the data in the employees table. A SELECT FIRST 3 rows is used to illustrate that in the case where employee_id is equal to 101, manager_id is updated to 100. The remaining 106 rows from the employees table are inserted into the contacts table:

Command> CREATE TABLE contacts (employee_id NUMBER (6) NOT NULL PRIMARY KEY, 
       > manager_id NUMBER (6));
Command> SELECT employee_id,manager_id FROM employees WHERE employee_id =101;
< 101, 100 >
1 row found.
Command> INSERT INTO contacts VALUES (101,null);
1 row inserted.
Command> SELECT COUNT (*) FROM employees;
< 107 >
1 row found.
Command> MERGE INTO contacts c
>    USING employees e
>     ON (c.employee_id = e.employee_id)
> WHEN MATCHED THEN
>      UPDATE SET c.manager_id = e.manager_id
> WHEN NOT MATCHED THEN
>     INSERT (employee_id, manager_id)
>             VALUES (e.employee_id, e.manager_id);
107 rows merged.
Command> SELECT COUNT (*) FROM contacts;
< 107 >
1 row found.
Command> SELECT FIRST 3 employee_id,manager_id FROM employees;
< 100, <NULL> >
< 101, 100 >
< 102, 100 >
3 rows found.
Command> SELECT FIRST 3 employee_id, manager_id FROM contacts;
< 100, <NULL> >
< 101, 100 >
< 102, 100 >
3 rows found.