MERGE

>>-MERGE INTO--+-------------------------------+--+-target_table---+--+---------------+-->
               |                          (1)  |  +-target_view----+  '-+----+--alias-'
               '-| Optimizer Directives |------'  '-target_synonym-'    '-AS-'
 
>--USING--+-source_table----+--+---------------+---------------->
          +-source_view-----+  '-+----+--alias-'
          '-source_subquery-'    '-AS-'
 
                     (2)
>--ON--| Condition |-------------------------------------------->
 
                                            (3)
>--WHEN MATCHED THEN UPDATE--| SET Clause |--------------------->
 
                                 .-,------.
                                 V        |                    (4)
>--WHEN NOT MATCHED THEN INSERT----column-+--| VALUES Clause |------><

Notes:

Element	Description	Restrictions	Syntax
alias	Temporary name that you declare here for the target table object or source table object	If potentially ambiguous, you must precede alias with the AS keyword	Identifier
column	Name of a column in the target table object in which to insert source data	Must exist in the target object	Identifier
source_table, source_view, source_subquery	Name of a table object (or the result of a query) that contains the data that you are relocating	Must exist	Database Object Name; SELECT, p. SELECT
target_table, target_view, target_synonym	Name or synonym of a table or updatable view in which to insert the source data	See Target Table Restrictions	Database Object Name

The MERGE statement enables you to merge data of a source table into a destination table, here called the target table. It provides an efficient way to update and insert the rows of the source table into the target table, based on search conditions that you specify within the statement.

The target object can be in a different database from the source object, but it must be a database managed by the currently running Extended Parallel Server instance. The source object, however, can be in a different database from the target object, and need not be a database managed by the current running Extended Parallel Server instance.

Rows in the target object that match the search conditions are updated; otherwise, new rows are inserted into the target object.

The MERGE statement combines some of the functionality of the UPDATE and INSERT statements into a single statement. The SET clause of this statement is identical to that of the UPDATE statement. For more information, see SET Clause.

The VALUES clause of the MERGE is similar to the INSERT statement, but only accepts column names and constant values, not expressions. For more information, see VALUES Clause.

The following example uses the transaction table new_sale to merge rows into the fact table sale, updating sale_count if there are already records in the sale table. If not, the rows are inserted into the sale table.

MERGE INTO sale USING new_sale AS n ON sale.cust_id = n.cust_id
   WHEN MATCHED THEN UPDATE SET sale.salecount = sale.salecount + n.salecount
   WHEN NOT MATCHED THEN INSERT (cust_id, salecount) 
      VALUES    (n.cust_id,n.salecount);

If an error occurs while MERGE is executing, the entire statement is rolled back.

If the constraints checking mode for the target table is set to IMMEDIATE, then unique and referential constraints of the target object are checked after all the UPDATE and INSERT operations are complete. The NOT NULL and check constraints are checked during the UPDATE and INSERT operations. If the checking mode is set to DEFERRED, the constraints are not checked until after the transaction is committed.

MERGE

Syntax

Usage