Skip Headers
Oracle® TimesTen In-Memory Database SQL Reference
Release 11.2.1

Part Number E13070-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

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:

Parameter Description
[Owner.]TargetTableName Name of the target table. This is the table into which rows are either updated or inserted.
[Alias] Optionally, you can specify an alias name for the target or source table.
USING {[Owner.]SourceTableName | (Subquery)} [Alias] The USING clause indicates the table name or the subquery that is used for the source of the data. Use a subquery if you wish to use joins or aggregates. Optionally, you can specify an alias for the table name or the subquery.
ON (Condition) You specify the condition that is used to evaluate each row of the target table to determine if the row should be considered for either a merge insert or a merge update. If the condition is true when evaluated, then the MergeUpdateClause is considered for the target row using the matching row from the SourceTableName. An error is generated if more than one row in the source table matches the same row in the target table. If the condition is not true when evaluated, then the MergeInsertClause is considered for that row.
SET SetClause Clause used with the UPDATE statement. For information on the UPDATE statement, see "UPDATE".
[WHERE Condition1] For each row that matches the ON (Condition), Condition1 is evaluated. If the condition is true when evaluated, then the row is updated. You can refer to either the target table or the source table in this clause. You cannot use a subquery. The clause is optional.
INSERT [Columns[,...]]VALUES ({{Expression |DEFAULT|NULL} [,...]}) Columns to insert into the target table. For more information on the INSERT statement, see "INSERT".
[WHERE Condition2] If specified, Condition2 is evaluated. If the condition is true when evaluated, then the row is inserted into the target table. The condition can refer to the source table only. You cannot use a subquery.

Description

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.