MERGE

This statement is not supported in TimesTen Scaleout.

In TimesTen Classic:

The MERGE statement enables 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 which 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.

Usage with TimesTen Scaleout

This statement is not supported with TimesTen Scaleout.

SQL syntax

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

The syntax for MergeUpdateClause is as follows: 

WHEN MATCHED THEN UPDATE SET SetClause [WHERE Condition1]

The syntax for MergeInsertClause is as follows: 

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

Parameters

Parameter Description

hint

Specifies a statement level optimizer hint for the MERGE statement. See "Statement Level Optimizer Hints" for information on statement level optimizer hints.

[Owner.]TargetTableName

Name of the target table. This is the table in which rows are either updated or inserted.

[Alias]

You can optionally 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 to use joins or aggregates. Optionally, you can specify an alias for the table name or the subquery.

ON (Condition)

Specify the condition 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. See "UPDATE" for information on the UPDATE statement.

[WHERE Condition1]

For each row that matches the ON (Condition), Condition1 is evaluated. If the condition is true when evaluated, 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. See "INSERT" for information on the INSERT statement.

[WHERE Condition2]

If specified, Condition2 is evaluated. If the condition is true when evaluated, the row is inserted into the target table. The condition can refer to the source table only. You cannot use a subquery.

Description

  • You can specify the MergeUpdateClause and MergeInsertClause together or separately. If you specify both, they can be 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 using 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 contacts with values 101 and NULL for employee_id and manager_id, respectively. The MERGE statement is used to insert rows into contacts 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 contacts: 

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.