DB2 10.5 for Linux, UNIX, and Windows

MERGE statement

The MERGE statement updates a target (a table or view, or the underlying tables or views of a fullselect) using data from a source (result of a table reference).

Rows in the target that match the source can be deleted or updated as specified, and rows that do not exist in the target can be inserted. Updating, deleting or inserting a row in a view updates, deletes or inserts the row in the tables on which the view is based.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared.

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following authorities:
  • If an insert operation is specified, INSERT privilege on the table or view; if a delete operation is specified, DELETE privilege on the table or view; and if an update operation is specified, either:
    • UPDATE privilege on the table or view
    • UPDATE privilege on each column that is to be updated
  • CONTROL privilege on the table
  • DATAACCESS authority
The privileges held by the authorization ID of the statement must also include at least one of the following authorities:
  • SELECT privilege on every table or view identified in the table-reference
  • CONTROL privilege on the tables or views identified in the table-reference
  • DATAACCESS authority
If search-condition, insert-operation, or assignment-clause includes a subquery, the privileges held by the authorization ID of the statement must also include at least one of the following authorities:
  • SELECT privilege on every table or view identified in the subquery
  • CONTROL privilege on the tables or views identified in the subquery
  • DATAACCESS authority
If a row-fullselect is included in the assignment, the privileges held by the authorization ID of the statement must include at least one of the following authorities for each referenced table, view, or nickname:
  • SELECT privilege
  • CONTROL privilege
  • DATAACCESS authority

If an expression that refers to a function is specified, the privilege set must include any authority that is necessary to execute the function.

Syntax

Read syntax diagramSkip visual syntax diagram
>>-MERGE INTO--+-table-name-------+----------------------------->
               +-view-name--------+   
               '-(--fullselect--)-'   

>--+------------------------+--USING--table-reference----------->
   '-| correlation-clause |-'                           

>--ON--search-condition----------------------------------------->

   .--------------------------------------------------------------------.   
   V                                                                    |   
>----WHEN--| matching-condition |--THEN--+-| modification-operation |-+-+-->
                                         '-signal-statement-----------'     

   .-ELSE IGNORE-.                     
>--+-------------+--+--------------+---------------------------><
                    '-WITH--+-RR-+-'   
                            +-RS-+     
                            +-CS-+     
                            '-UR-'     

correlation-clause

   .-AS-.                                                
|--+----+--correlation-name--+-----------------------+----------|
                             |    .-,-----------.    |   
                             |    V             |    |   
                             '-(----column-name-+--)-'   

matching-condition

|--+-----+--MATCHED--+-----------------------+------------------|
   '-NOT-'           '-AND--search-condition-'   

modification-operation

|--+-| update-operation |-+-------------------------------------|
   +-| delete-operation |-+   
   '-| insert-operation |-'   

update-operation

|--UPDATE--+---------------+--SET--| assignment-clause |--------|
           '-period-clause-'                               

assignment-clause

   .-,----------------------------------------------------------------.   
   V                                                                  |   
|----+-column-name-- = --+-expression-+-----------------------------+-+--|
     |                   +-DEFAULT----+                             |     
     |                   '-NULL-------'                             |     
     |    .-,-----------.               .-,------------------.      |     
     |    V             |               V              (1)   |      |     
     '-(----column-name-+--)-- = --(--+---+-expression-----+-+-+--)-'     
                                      |   +-DEFAULT--------+   |          
                                      |   '-NULL-----------'   |          
                                      |                (2)     |          
                                      '-row-fullselect---------'          

delete-operation

|--DELETE--+---------------+------------------------------------|
           '-period-clause-'   

insert-operation

|--INSERT--+-----------------------+---------------------------->
           |    .-,-----------.    |   
           |    V             |    |   
           '-(----column-name-+--)-'   

>--VALUES--+-+-expression-+-----------+-------------------------|
           | +-DEFAULT----+           |   
           | '-NULL-------'           |   
           |    .-,--------------.    |   
           |    V                |    |   
           '-(----+-expression-+-+--)-'   
                  +-DEFAULT----+          
                  '-NULL-------'          

period-clause

|--FOR PORTION OF BUSINESS_TIME--FROM--value1--TO--value2-------|

Notes:
  1. The number of expressions, NULLs, and DEFAULTs must match the number of column names.
  2. The number of columns in the select list must match the number of column names.

Description

table-name, view-name, or (fullselect)
Identifies the target of the update, delete, or insert operations of the merge. The name must identify a table or view that exists at the current server, but it must not identify a catalog table, a system-maintained materialized query table, a view of a catalog table, a read-only view, or a view that directly or indirectly contains a WHERE clause that references a subquery or a routine defined with NOT DETERMINISTIC or EXTERNAL ACTION (SQLSTATE 42807).

If the target of the merge operation is a fullselect, the fullselect must be updatable, deletable, or insertable as defined in the "Updatable views", "Deletable views", or "Insertable views" Notes items in the description of the CREATE VIEW statement.

You cannot use a period-clause in an update-operation or a delete-operation if the target of the merge operation is a union-all view or a fullselect.

You cannot use a nickname (a reference to a remote, federated table) as the target table.

correlation-clause
Can be used within search-condition or on the right side of an assignment-clause to designate a table, view, or fullselect. For a description of correlation-clause, see "table-reference" in the description of "Subselect".
USING table-reference
Specifies a set of rows as a result table to be merged into the target. If the result table is empty, a warning is returned (SQLSTATE 02000).
ON search-condition
Specifies which rows from table-reference are to be used in the update and delete operation of the merge, and which rows are to be used in the insert operation of the merge. The search-condition is applied to each row of the target table and result table of the table-reference. For those rows of the result table of the table-reference where the result of the search-condition is true, the specified update or delete operation is performed. For those rows of the result table of the table-reference where the result of the search-condition is not true, the specified insert operation is performed.
The search-condition has the following restrictions (SQLSTATE 42972 unless otherwise noted):
  • It cannot contain any subqueries, scalar or otherwise
  • It cannot include any dereference operations or the DEREF function where the reference value is other than the object identifier column
  • It cannot include an SQL function
  • It cannot include an XMLQUERY or XMLEXISTS expression
  • Any column that is referenced in an expression of the search-condition must be a column of the target table, view, or table-reference
  • Any function that is referenced in an expression of the join-condition of a full outer join must be deterministic and have no external action
  • It cannot be include an aggregate function (SQLSTATE 42903)

If the search-condition is false or unknown for every row in table-reference, a warning is returned (SQLSTATE 02000).

WHEN matching-condition
Specifies the condition under which the modification-operation or the signal-statement is executed. Each matching-condition is evaluated in order of specification. Rows for which the matching-condition evaluates to true are not considered in subsequent matching conditions.
MATCHED
Indicates the operation to be performed on the rows where the ON search condition is true. Only UPDATE, DELETE, or signal-statement can be specified after THEN.
AND search-condition
Specifies a further search condition to be applied against the rows that matched the ON search condition for the operation to be performed after THEN.
NOT MATCHED
Indicates the operation to be performed on the rows where the ON search condition is false or unknown. Only INSERT or signal-statement can be specified after THEN.
AND search-condition
Specifies a further search condition to be applied against the rows that did not match the ON search condition for the operation to be performed after THEN.
THEN modification-operation
Specifies the operation to execute when the matching-condition evaluates to true.
update-operation
Specifies the update operation to be executed for the rows where the matching-condition evaluates to true.
UPDATE
Introduces the update operation.
period-clause
Specifies that a period clause is applied to the update operation in the MERGE statement. For more information about the effects of a period clause specified in the context of an update operation, see the UPDATE statement topic.
SET
Introduces the assignment of values to column names.
assignment-clause
Specifies a list of column updates.
column-name
Identifies a column to be updated. The column-name must identify a column of the specified table or view, but not a view column derived from a scalar function, constant, or expression. A column must not be specified more than once (SQLSTATE 42701).

A view column derived from the same column as another column of the view can be updated, but both columns cannot be updated in the same MERGE statement (SQLSTATE 42701).

expression
Indicates the new value of the column. The expression must not include an aggregate function except when it occurs within a scalar fullselect (SQLSTATE 42903).

An expression can contain references to columns of the table-name or view-name. For each row that is updated, the value of such a column in an expression is the value of the column in the row before the row is updated.

If expression is a reference to a single column of the source table, the source table column value may have been specified with an extended indicator variable value. The effects of such indicator variables apply to the corresponding target columns of the assignment-clause.

If expression is a single host variable, or a host variable being explicitly cast, the host variable can include an indicator variable that is enabled for extended indicator variables.

When extended indicator variables are enabled, the extended indicator variable values of default (-5) or unassigned (-7) must not be used (SQLSTATE 22539) if either of the following statements is true:

  • The expression is more complex than a single host variable with explicit casts
  • The target column has data type of structured type
DEFAULT
The default value assigned to the column. DEFAULT can be specified only for columns that have a default value. For information about default values of data types, see the description of the DEFAULT clause in the "CREATE TABLE" statement.

DEFAULT must be specified for a column that was defined as GENERATED ALWAYS. A valid value can be specified for a column that was defined as GENERATED BY DEFAULT.

NULL
Specifies the null value as the new value of the column. Specify NULL only for nullable columns (SQLSTATE 23502).
row-fullselect
Specifies a fullselect that returns a single row. The result column values are assigned to each corresponding column-name. If the fullselect returns no rows, the null value is assigned to each column; an error occurs if any column to be updated is not nullable. An error also occurs if there is more than one row in the result.

A row-fullselect can contain references to columns of the target table of the MERGE statement. For each row that is updated, the value of such a column in an expression is the value of the column in the row before the row is updated. An error is returned if there is more than one row in the result (SQLSTATE 21000).

delete-operation
Specifies the delete operation to be executed for the rows where the matching-condition evaluates to true.
DELETE
Introduces the delete operation.
period-clause
Specifies that a period clause is applied to the delete operation in the MERGE statement. For more information about the effects of a period clause specified in the context of a delete operation, see the DELETE statement topic.
insert-operation
Specifies the insert operation to be executed for the rows where the matching-condition evaluates to true.
INSERT
Introduces a list of column names and row value expressions to be used for the insert operation.

The number of values for the row in the row value expression must equal the number of names in the insert column list. The first value is inserted in the first column in the list, the second value in the second column, and so on.

(column-name,...)
Specifies the columns for which the insert values are provided. Each name must identify a column of the table or view. The same column must not be identified more than once (SQLSTATE 42701). A view column that cannot accept insert values must not be identified. A value cannot be inserted into a view column that is derived from:
  • A constant, expression, or scalar function
  • The same base table column as some other column of the view

If the object of the operation is a view with such columns, a list of column names must be specified, and the list must not identify these columns.

Omission of the column list is an implicit specification of a list in which every column of the table (that is not defined as implicitly hidden) or view is identified in left-to-right order. This list is established when the statement is prepared, and therefore does not include columns that were added to a table after the statement was prepared.

VALUES
Introduces one or more rows of values to be inserted.
expression
Any expression that does not include a column name (SQLSTATE 42703).

If expression is a reference to a single column of the source table, the source table column value may have been specified with an extended indicator variable value. The effects of such indicator variables apply to the corresponding target columns of the insert-operation.

If expression is a single host variable, or a host variable being explicitly cast, the host variable can include an indicator variable (or in the case of a host structure, an indicator array) that is enabled for extended indicator variables.

When extended indicator variables are enabled, the extended indicator variable values of default (-5) or unassigned (-7) must not be used (SQLSTATE 22539) if either of the following statements is true:

  • The expression is more complex than a single host variable with explicit casts
  • The target column has data type of structured type
DEFAULT
The default value assigned to the column. DEFAULT can be specified only for columns that have a default value. For information about default values of data types, see the description of the DEFAULT clause in the "CREATE TABLE" statement.

DEFAULT must be specified for a column that was defined as GENERATED ALWAYS. A valid value can be specified for a column that was defined as GENERATED BY DEFAULT.

NULL
Specifies the null value as the value of the column. Specify NULL only for nullable columns (SQLSTATE 23502).
signal-statement
Specifies the SIGNAL statement that is to be executed to return an error when the matching-condition evaluates to true.
ELSE IGNORE
Specifies that no action is to be taken for the rows where no matching-condition evaluates to true. If all rows of table-reference are ignored, a warning is returned (SQLSTATE 02000).
WITH
Specifies the isolation level at which the MERGE statement is executed.
RR
Repeatable Read
RS
Read Stability
CS
Cursor Stability
UR
Uncommitted Read
The default isolation level of the statement is the isolation level of the package in which the statement is bound.

Rules

For other rules that affect the update, insert, or delete operation portion of the MERGE statement, see the "Rules" section of the corresponding statement description.

Notes

Examples