MERGE statement

FL 500The MERGE statement updates a target (a table or view) using data from a source (the result of a table reference or the specified input data). Rows in the target that match the input data 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 into a view updates, deletes, or inserts the row into the tables on which the view is based, if no INSTEAD OF trigger is defined on this view.

Invocation for MERGE

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization for MERGE

The privileges that are held by the privilege set that is defined below must include at least one of the following privileges:

SYSADM authority
Ownership of the table
DATAACCESS authority
If the search condition contains a reference to a column of the table or view, the SELECT privilege for the referenced table or view
If the delete operation is specified, the DELETE privilege for the table or view
If the insert operation is specified, the INSERT privilege for the table or view
If the update operation is specified, at least one of the following privileges is required:
- the UPDATE privilege for the table or view
- the UPDATE privilege on each column that is updated
- If the right side of the assignment clause contains a reference to a column of the table or view, the SELECT privilege for the referenced table or view

If the database is implicitly created, the database privileges must be on the implicit database or on DSNDB04.

If the insert operation or assignment clause includes a subquery, the privileges that are held by the privilege set must also include at least one of the following privileges:

SYSADM authority
The SELECT privilege on every table or view that is identified in the subquery
Ownership of the tables or views that are identified in the subquery
DATAACCESS authority

Privilege set: If the statement is embedded in an application program, the privilege set is the privileges that are held by the owner of the plan or package. If the statement is dynamically prepared, the privilege set is determined by the DYNAMICRULES behavior in effect (run, bind, define, or invoke) and is summarized in Table 1. (For more information on these behaviors, including a list of the DYNAMICRULES bind option values that determine them, see Authorization IDs and dynamic SQL.)

Syntax for MERGE

Notes:

¹ The rules for specifying this clause are affected by the NOT ATOMIC clause or the USING clause with source-values. For more information, see Rules for MERGE statements.

correlation-clause:

include-columns:

data-type:

built-in-type:

source-values:

Notes:

values-single-row:

values-multiple-row:

Notes:

¹ For a static MERGE statement, if FOR n ROWS is not specified, values-multiple-row is treated as values-single-row. For a dynamic MERGE statement, FOR n ROWS does not need to be specified in the MERGE statement. It can be specified in the EXECUTE statement, but cannot be specified in both the MERGE and EXECUTE statements.

matching-condition:

Notes:

¹ The rules for specifying this clause are affected by the NOT ATOMIC clause or the USING clause with source-values. For more information, see Rules for MERGE statements.

modification-operation:

Notes:

¹ The rules for specifying this clause are affected by the NOT ATOMIC clause or the USING clause with source-values. For more information, see Rules for MERGE statements.

assignment-clause:

Notes:

¹ The number of expressions, DEFAULT, and NULL keywords must match the number of column-names.
² The number of columns in the SELECT list must match the number of column-names.
³ The rules for specifying this clause are affected by the NOT ATOMIC clause or the USING clause with source-values. For more information, see Rules for MERGE statements..

update-operation:

delete-operation:

insert-operation:

Notes:

¹ The number of expressions, DEFAULT, and NULL keywords must match the number of column-names.
² The number of columns in the SELECT list must match the number of column-names.

Description for MERGE

INTO table-name or view-name

FL 500Identifies the target of the update, insert, or delete operations of the merge. The name must identify a table or view that exists at the current server. The name must not identify: End of change

A catalog table
A directory table
A created global temporary table
A read-only view
A system-maintained materialized query table
A table that is implicitly created for an XML column
An accelerator-only table

If a view is specified as the target of the MERGE statement, the view must not be defined with any INSTEAD OF triggers.

AS correlation-name

FL 500correlation-name provides an alternative name that can be used when referencing columns of target table.

The correlation name can be used within search-condition, matching-condition, or as part of a source value for an assignment, to designate the target table or view. The correlation-name is used to qualify references to the columns of the table or view. For a description of correlation-clause, see table-reference.

column-name: Specifies a column name. When a correlation-name is specified, column-names can also be specified to give names to the columns of the target table-name or view-name. If a column list is specified, there must be a name in the column list for each column in the table or view.

include-columns

Specifies a set of columns that are included, along with the columns of the specified table or view, in the result table of the MERGE statement when it is nested in the FROM clause of the outer fullselect that is used in a SELECT statement, or in a SELECT INTO statement. The included columns are appended to the end of the list of columns that are identified by table-name or view-name. If a value is not specified for an included column, a null value is returned for that column.

INCLUDE

Introduces a list of columns that is to be included in the result table of the MERGE statement. The included columns are only available if the MERGE statement is nested in the FROM clause of a SELECT statement or a SELECT INTO statement. INCLUDE can only be specified when the MERGE statement is nested in the FROM clause of a SELECT statement.

column-name

Specifies the name for a column of the result table of the MERGE statement that is not the same name as another included column or a column in the table or view that is specified in table-name or view-name.

data-type

Specifies the data type of the included column. The included columns are nullable.

Columns with the following data types can not be used as INCLUDE columns:

LONG VARCHAR,
LONG VARGRAPHIC,
XML
LOBs
distinct types that are based on any of the listed data types.

built-in-type: Specifies a built-in data type. See CREATE TABLE statement for a description of each built-in type.
The CCSID 1208 and CCSID 1200 clauses must not be specified for an INCLUDE column.
distinct-type: Specifies a distinct type. Any length, precision, or scale attributes for the column are those of the source type of the distinct type as specified by using the CREATE TYPE statement.

USING

FL 500Specifies a set of rows as a result table to be merged into the target.

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.

Start of change If table-reference is specified, the NOT ATOMIC clause and VALUES values-multiple-row must not be specified. For more information, see Rules for MERGE statements. End of change

source-values

VALUES values-single-row or values-multiple-row

Specifies the values for a set of rows as a result table to be merged into the target. values-single-row specifies a single row of source data. values-multiple-row specifies multiple rows of source data. Start of change The number of values must not exceed 750. End of change

Start of change If VALUES values-single-row or VALUESvalues-multiple-row is specified, table-reference must not be specified, and NOT ATOMIC CONTINUE ON SQLEXCEPTION must be specified ifVALUES values-multiple-row is specified. For more information, see Rules for MERGE statements. End of change

expression

Specifies an expression of the type that is described in Expressions. The expression must not include a column name. The expression must not reference a NEXT VALUE or PREVIOUS VALUE expression. If the expression is a single host variable, the host variable can identify a structure. Any host variable or structure that is specified must be described in the application program according to the rules for declaring host structures and variables.

host-variable-array

Specifies a host-variable array. Each host-variable array must be defined in the application program in accordance with the rules for declaring an array. A host-variable array contains the data to merge into a target column. The number of rows must be less than or equal to the dimension of each of the host-variable arrays. An optional indicator array can be specified for each host-variable array. An indicator array should be specified if the SQLTYPE of any SQLVAR occurrence indicates that a column is nullable. The indicator array can be enabled for extended indicator variables. The dimension of the indicator array must be large enough to contain an indicator for each row of input data.

A host structure is not supported in host-variable-array.

host-variable-array is supported in C/C++, COBOL, and PL/I. For more information, see Host-variable arrays in PL/I, C, C++, and COBOL.

NULL

Specifies a null value.

The column-name that is specified in source-values corresponding to the NULL keyword is used to determine the data type of the null value. The column-name that is specified in source-values must be referenced elsewhere in the MERGE statement such that its data type can be determined from the context in which it is used, and all such references must resolve to the same data type. References to date-time data types and CHAR or VARCHAR are considered the same type.

FOR host-variable or integer-constant ROWS

Specifies the number of rows to merge. For a dynamic MERGE statement, this clause can be specified on the EXECUTE statement. host-variable or integer-constant is assigned to a value k. If host-variable is specified, it must be an exact numeric type with a scale of zero and must not include an indicator variable. k must be in the range 1–32767. k rows are merged into the target from the specified source data.

If a parameter marker is specified in FOR n ROWS, a value must be provided with the USING clause of the associated EXECUTE statement.

AS correlation-name

Specifies a correlation name for the source-values. The correlation-name is used to qualify references to the columns of source-values.

column-name: Specifies a column name. When a correlation-name is specified, column-names can also be specified to give names to the columns of source-values for an assignment. If a column list is specified, there must be a name in the column list for each column in source-values.

ON search-condition

Specifies the predicates that are used to determine whether a row from table-reference or source-values matches rows in the target table.

Each column-name in the search condition must name a column of the target table or view, or table-reference or source-values. If a column-name exists in both the target and the source-values, the column name must be qualified.

Start of change Logically, a right join is performed between the target table and the table-reference (or source-values) using the ON search-condition. For those rows of the join result table where the search condition is true, the specified update or delete operation is performed. For those rows of the join result table where the result of the search condition is not true, the specified insert operation is performed. End of change

A subquery is not allowed in the search-condition of the ON clause. The search-condition of the ON clause cannot contain expressions that use aggregate functions or non-deterministic scalar functions.

The search-condition of the ON clause must not include an IN predicate that includes a fullselect, or a quantified predicate that includes a fullselect.

WHEN matching-condition

FL 500Specifies the condition under which the modification-operation or the signal-statement is executed. Each matching-condition is evaluated in order of specification.

Start of change When the NOT ATOMIC clause is not specified, rows for which the matching-condition evaluates to true are not considered in subsequent matching conditions. For more information, see Rules for MERGE statements. End of change

MATCHED

Indicates the operation to perform on the rows where the ON search-condition is true. Only UPDATE, DELETE, or signal-statement can be specified after the THEN clause.

Start of change If the NOT ATOMIC clause or USING source-values is specified, WHEN MATCHED must not be specified more than one time. For more information, see Rules for MERGE statements. End of change

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. The search-condition must not include an IN predicate that includes a fullselect, or a quantified predicate that includes a fullselect.

NOT MATCHED

Indicates the operation to perform on the rows where the ON search-condition is false or unknown, or the target table is empty. Only INSERT or signal-statement can be specified after the THEN clause, and must not reference a column of the target table.

Start of change If the NOT ATOMIC clause or USING source-values is specified, WHEN NOT MATCHED must not be specified more than one time. For more information, see Rules for MERGE statements. End of change

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. The search-condition must not include a column of the target table.

THEN modification-operation

FL 500Specifies the operation to be executed when the matching-condition evaluates to true.

update-operation

Specifies the update operation to be executed when the matching-condition evaluates to true.

UPDATE

Introduces the update operation.

Start of change When extended indicators are enabled, a column of the source table must not be referenced multiple times in a single modification-operation. Extended indicators are enabled when EXTENDEDINDICATOR(YES) is used, or when the WITH EXTENDED INDICATORS prepare attribute has been specified for the MERGE statement. End of change

When NOT ATOMIC CONTINUE ON SQLEXCEPTION is specified, or the NOT ATOMIC CONTINUE ON SQLEXCEPTION clause is not specified, and source-values (VALUES) is specified, the rows that are updated from a source row are subject to more updates by subsequent source rows in the same statement. The update is cumulative.

An update-operation in a MERGE statement does not reset the AREO* status on a table.

SET

Introduces the assignment of values to column names.

assignment-clause

FL 500Specifies a list of column updates.

If row-fullselect is specified, the number of columns in the result of row-fullselect must match the number of column-names that are specified. If row-fullselect is not specified, the number of expressions, and NULL and DEFAULT keywords must match the number of column-names that are specified.

column-name

Identifies a column to update. column-name must identify a column of the specified table or view, and that column must be updatable. The column must not be a generated column, or a column of a view that is derived from a scalar function, a constant, or an expression. column-name can also identify an included column. The same column-name must not be specified more than one time. A view column that is 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.

Assignments to included columns are only processed when the MERGE statement is nested in the FROM clause of a SELECT statement or a SELECT INTO statement. There must be at least one assignment clause that specifies a column-name that is not an included column.

expression

Specifies the new value of the column. The expression is any expression of the type that is described in Expressions. The expression must not include an aggregate function.

The expression can contain references to columns of table-name, view-name, table-reference, or source-values. For each row that is updated, the value of a target column in an expression is the value of the column in the row before the row is updated. expression cannot contain references to an included column.

Start of change If expression is a reference to a single column of the source table, the source table column value might have been specified with an extended indicator value. The effects of extended indicator values apply to the corresponding target columns of the assignment-clause. End of change

Start of change When extended indicators are enabled, the extended indicator values of DEFAULT (-5) or UNASSIGNED (-7) must not be used if expression is more complex than the following references: End of change

A single column of the source table
A single host variable

DEFAULT

Specifies the default value for the column. DEFAULT can be specified only for columns that have a default value. Start of change

DEFAULT must not be specified for a ROWID column. End of change

The value that is assigned depends on how the column is defined. For more information about default values, see the description of the DEFAULT clause in CREATE TABLE statement.

Start of change 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. End of change

NULL

Specifies the null value as the new value of the column. Specify NULL only for nullable columns.

row-fullselect

Specifies a fullselect that returns a single row. The column values are assigned to each of the corresponding column-names. If the fullselect returns no rows, the null value is assigned to each column; an error occurs if any column that is to be updated is not nullable.

If the fullselect refers to columns that are to be updated, the value of such a column in the fullselect is the value of the column in the row before the row is updated.

delete-operation

Specifies the delete operation that is to be executed for the rows where the matching-condition evaluates to true.

DELETE: Introduces the delete operation.

insert-operation

Specifies the insert operation that is to be executed for the rows where the matching-condition evaluates to true.

INSERT

Specifies a list of column names and row value expressions to use for the insert operation.

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

(column-name,...)

Specifies the column 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.

Start of change If extended indicators are not enabled, a view column that is not updatable must not be identified. If extended indicator variables are not enabled and the object of the insert operation is a view with non-updatable columns, a list of column names must be specified, and the list must not identify those columns. For an explanation of updatable columns in views, see CREATE VIEW statement. End of change

If an included column is not specified in the list of column names, the value of the included column is set to null. The column list cannot contain only included columns.

A view column that cannot accept insert values must not be specified. A value cannot be inserted into a view column that is derived from one of the following items:

A constant, an expression, or a scalar function
The same column of the base table as another column of the view

If the object of the operation is a view that contains columns that cannot accept insert values, a list of column names must be specified and the list must not specify 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 the table after the statement was prepared.

VALUES

Introduces one or more rows of values to insert.

expression

Specifies an expression that does not include a column name of the target. If expression is a host variable, the host variable can include an indicator variable, or in the case of a host structure, an indicator array. Start of change

When extended indicators are enabled, the extended indicator values of DEFAULT (-5) or UNASSIGNED (-7) must not be used if expression is more complex than the following references: End of change

A single column of the source table
A single host variable
A host variable that is being explicitly cast

In addition, a CAST specification can be used if either:

The target column is defined as nullable.
The target column is defined as NOT NULL with a non-null default, the source of the CAST specification is a single host variable, and the data attributes (data type, length, precision, and scale) of the host variable are the same as the result of the cast specification.

DEFAULT

Specifies the default value for the column. DEFAULT must only be specified for columns that have a default value. The value that is assigned depends on how the column is defined. For more information about default values, see the descriptions of the DEFAULT clause in CREATE TABLE statement and in INSERT statement.

If the column is specified in the INCLUDE column list, the column value is set to null.

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

NULL

Specifies the null value as the value of the column. Specify NULL only for nullable columns.

signal-statement

Specifies the SIGNAL statement that is to be executed to return an error when the matching-condition evaluates to true. End of change

ELSE IGNORE

FL 500Specifies 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. End of change

NOT ATOMIC CONTINUE ON SQLEXCEPTION

The rows of input data are processed separately. Any statement level triggers are processed for each row of source data that is processed, and the transition table includes the individual row that was processed. When errors are encountered and this option is in effect, processing continues, and some of the specified rows will not be processed. In this case, if an appropriate trigger is defined on the underlying base table, the statement level trigger will only be activated for rows that were successfully processed.

Regardless of the failure of any particular source row, the MERGE statement will not undo any changes that are made to the database by the statement. Merge will be attempted for rows that follow the failed row. However, the minimum level of atomicity is at least that of a single source row (that is, it is not possible for a partial merge to complete), including any triggers that might have been activated as a result of the MERGE statement.

Start of change Although the documentation sometimes uses the abbreviation "NOT ATOMIC clause" for readability, the NOT ATOMIC CONTINUE ON SQLEXCEPTION clause must always be fully specified when it is used. End of change

Start of change If the NOT ATOMIC clause is specified, table-reference must not be specified. For more information, see Rules for MERGE statements. End of change

QUERYNO integer

Specifies the number for this SQL statement that is used in EXPLAIN output and trace records. The number is used for the QUERYNO column of the plan table for the rows that contain information about this SQL statement. This number is also used in the QUERYNO column of the SYSIBM.SYSSTMT and SYSIBM.SYSPACKSTMT catalog tables.

If QUERYNO is not specified, the number that is associated with the SQL statement is the statement number that is assigned during precompilation. Thus, if the application program is changed and then precompiled, the statement number might change.

Rules for MERGE statements

FL 500If table-reference is specified the following rules apply:

More than one modification-operation (update-operation, delete-operation, or insert-operation), or signal-statement can be specified in a single MERGE statement.
Each row in the target can only be operated on once. A row in the target can only be identified as MATCHED with one row in the result table of the table-reference. A nested SQL operation (RI or trigger except INSTEAD OF trigger) cannot specify the target table (or a table within the same table hierarchy) as a target of an UPDATE, DELETE, INSERT, or MERGE statement.
A row that is inserted by the MERGE statement cannot also be updated by that MERGE statement. There is no attempt to update a row in the target that did not already exist before the MERGE statement was executed.
If an error occurs during execution of the MERGE statement, the whole statement is rolled back.

If the NOT ATOMIC clause¹ is specified, or if the NOT ATOMIC clause is omitted but USING source-values (VALUES) is specified, the following rules apply:

A MERGE statement can contain one update-operation and one insert-operation.
The rows that are updated from a source row are subject to more updates by subsequent source rows in the same statement. The update is cumulative.
When NOT ATOMIC CONTINUE ON SQLEXCEPTION is specified, the rows of source data are processed separately. Any references to special registers, sequence expressions, and functions in the MERGE statement are evaluated as each row of source data is processed. Statement-level triggers are activated as each row of source data is processed.
If one or more errors occur during the operation for a row of source data, processing continues. The row that was being processed at the time of the error is not inserted or updated. Execution continues with the next row to be processed, and any other changes that are made during the execution of the multiple-row MERGE statement are not backed out. However, the processing of an individual row is an atomic action.
The following clauses must not be specified:
- table-reference
- correlation-clause that contains a list of column names
- AND search-condition
- delete-operation
- SIGNAL
- ELSE IGNORE
- row-fullselect
The WHEN MATCHED clause must not be specified more than one time.
The WHEN NOT MATCHED clause must not be specified more than one time.

For other rules that affect the update, insert, or delete portion of the MERGE statement, see the rules section of the corresponding statement description, in the following topics:

Notes for MERGE

Considerations for the NOT ATOMIC clause or USING source-values

FL 500When the NOT ATOMIC clause¹ is specified, or the NOT ATOMIC clause is not specified and USING source-values is specified with VALUES, the following clauses must not be specified:

table-reference
correlation-clause that contains a list of column names
AND search-condition
delete-operation
SIGNAL
ELSE IGNORE
row-fullselect

The following rules also apply in this situation:

The WHEN MATCHED clause must not be specified more than one time.
The WHEN NOT MATCHED clause must not be specified more than one time.

Tip:

Although the documentation sometimes uses the abbreviation "NOT ATOMIC clause" for readability, the NOT ATOMIC CONTINUE ON SQLEXCEPTION clause must always be fully specified when it is used.

Logical order of processing for a not-atomic MERGE statement

FL 500For a not-atomic MERGE statement (a MERGE statement that includes the NOT ATOMIC CONTINUE ON SQLEXCEPTION clause, or the NOT ATOMIC CONTINUE ON SQLEXCEPTION clause is not specified and source-values (VALUES) is specified), each source row is processed independently, as if a separate MERGE statement were executed for each source row. For example, a source row that causes an update of a target row activates any triggers (including statement-level triggers) when the update of the row is performed. Thus, if five rows are updated, any update triggers (including statement-level update triggers) are activated five times.

For a not-atomic MERGE statement, the logical order of processing for each row is:

Determine the row that is to be processed from the source and target.
Use the ON clause to classify these rows as either MATCHED or NOT MATCHED.
Evaluate any expression in any assignment-clause and insert-operation.
Apply the modification-operation to the applicable row. The triggers that are activated by the modification-operation are executed for the modification-operation. Statement-level triggers are activated even if no rows satisfy the modification-operation.

Logical order of processing for an atomic MERGE statement

FL 500For an atomic MERGE statement (the NOT ATOMIC CONTINUE ON SQLEXCEPTION clause is not specified), the source rows are processed as if a set of rows is processed by each WHEN clause. Thus, if five rows are updated, any row-level update triggers are activated five times. In addition, n statement-level update triggers are activated , where n is the number of WHEN clauses that contain an UPDATE, including any WHEN clauses that contain an UPDATE that did not process any of the source rows.

For an atomic MERGE, the logical order of processing is:

Determine the set of rows that are to be processed from the source and target. If any of the special registers CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP are used in this statement, only one clock reading is done for the whole statement.
Use the ON clause to classify these rows as either MATCHED or NOT MATCHED.
Evaluate any matching-condition in the WHEN clauses.
Evaluate any expression in any assignment-clause and insert-operation.
Execute each signal-statement.
Apply each modification-operation to the applicable rows in the order of specification. The triggers that are activated by each modification-operation are executed for the modification-operation. Statement-level triggers are activated even if no rows satisfy the modification-operation. Each modification-operation can affect the triggers and referential constraints of each subsequent modification-operation.

Trigger considerations

A MERGE statement might cause triggers to be activated. A trigger might cause other statements to be executed or raise error conditions depending on the source data values. A before-update or before-insert trigger processes immediately before the update or insert operation.

If a source row results in an insert, any after-insert triggers are activated after the insert operation completes.

If a source row results in updates, any after-update triggers are activated after all of the update operations complete.

Start of change If a source row results in deletes, any after-delete triggers are activated after all of the delete operations complete. End of change

Number of rows updated

FL 500After a MERGE statement is executed, the ROW_COUNT statement information item in the SQL Diagnostics Area (or SQLERRD(3) of the SQLCA) is the number of rows that are operated on by the MERGE statement, excluding rows that are identified by the ELSE IGNORE clause. The ROW_COUNT item and SQLERRD(3) do not include the number of rows that were operated on as a result of triggers.

For a description of ROW_COUNT, see GET DIAGNOSTICS statement. For a description of the SQLCA, see SQL communication area (SQLCA).

SQLCA and GET DIAGNOSTICS considerations

The GET DIAGNOSTICS statement can be used immediately after the MERGE statement to check which input rows fail during the merge operation. The GET DIAGNOSTICS statement information item, NUMBER, indicates the number of conditions that are raised. The GET DIAGNOSTICS condition information item, DB2_ROW_NUMBER, indicates the input source rows that cause an error.

Considerations for a MERGE without a column list in insert-operation

A MERGE statement without a specified column list as part of insert-operation does not include implicitly hidden columns. Therefore, such columns must have a defined default value.

DRDA considerations when the NOT ATOMIC clause is specified or the NOT ATOMIC clause is omitted and source-values (VALUES) is specified

Db2 Connect Version 9.1 and later releases support the MERGE statement. The support is for CLI only, with no embedded static SQL support.

When you run a MERGE statement at a Db2 for z/OS® requester, cases might exist where the requester does not know the number of rows in the source table. This situation includes the following cases:

For static or dynamic MERGE statements, of the FOR n ROWS clause contains a constant value for n.
For dynamic MERGE statements, of host variables are specified on the USING clause of an EXECUTE statement.

For both of these cases, if the number of rows in the source table is not known, the requester might send more data than is required to the server. The number of rows that are processed is correct because the server knows the correct numbers of rows to process. However, performance might be adversely affected. Consider the following example:

...long serial num [10];
struct { short len;
char data [18];
       }
name[20]...
EXEC SQL 
MERGE INTO T1
  USING (VALUES (:serial_num, :name))
  FOR 5 ROWS...

When this statement is run at the requester, the number of rows to merge (five) is not known. As a result, the requester sends 10 values for serial-name and name to the server because 10 is the size of the smallest host-variable array and is, therefore, the maximum number of rows that can merge without causing an error.

Do the following to help minimize performance problems:

Avoid using numeric constants in the FOR n ROWS clause of the MERGE statement. For static MERGE statements, avoiding numeric constants ensures that the values for n are known at the requester.
For dynamic MERGE statements, use the USING DESCRIPTOR clause instead of the USING host-variable clause on the EXECUTE statement. If a USING DESCRIPTOR clause is used on the EXECUTE statement, the value for n must be indicated in the descriptor.
If either of the previous methods cannot be used, perform the following actions:
- Make your host-variable arrays as small as possible, or declare that the size of your host-variable arrays are the size of n in the descriptor. This action avoids sending many unused host-variable array entries to the server.
- Ensure that varying length string arrays are initialized to a length of 0 (zero). Doing so minimizes the amount of data that is sent to the server.
- Ensure that decimal host-variable arrays are initialized to valid values. Doing so causes the requester to avoid sending a negative SQLCODE if the requester encounters invalid decimal data.

Extended indicators usage

When extended indicators are enabled, indicator values other than positive values and 0 (zero) through -7 must not be specified. The DEFAULT and UNASSIGNED extended indicator values must not appear in contexts where they are not supported. End of change

Extended indicators

In an update operation of the MERGE statement:

An extended indicator value of UNASSIGNED has the same effect as if it had not been specified in the statement.
An extended indicator value of DEFAULT must not be specified for a row-begin, row-end, transaction-start-ID, or generated expression column.
An extended indicator value of UNASSIGNED must not be assigned to all of the target columns.

In an insert operation of the MERGE statement, an extended indicator value of UNASSIGNED has the effect of setting the column to its default value.

Extended indicators and update triggers

If a target column is assigned an extended indicator value of unassigned, that column is not considered to have been updated. That column is treated as if it had not been specified in the OF column-name list of any update trigger that is defined on the target table.

Extended indicators and insert triggers

The activation of insert triggers is not affected by the use of extended indicator variables. Suppose that all columns in the implicit or explicit column list are assigned an extended indicator value of unassigned or default. Then, assume that an insert operation where all columns are assigned to the respective default values is attempted. If that operation is successful, the insert trigger is activated.

Table space data compression during an insert operation

If the table space is defined with compression, and data is inserted into a table in the table space, the first rows are stored uncompressed. When a amount of data that is determined by Db2 is inserted into the table, a compression dictionary is created and stored in the table space. The rows that are inserted into the table after the dictionary is created are stored compressed by using the compression dictionary.

System-period temporal tables

When a MERGE statement is processed for a system-period temporal table, the rows are affected in the same way as if the specific data change operation was invoked.

Archive-enabled tables

Consider the case when the target of a MERGE statement is an archive-enabled table, and the merge operation includes an insert or update operation. In this case, the involved rows are affected in the same way as if the insert or update operation was directly invoked on the table.

Start of change When the global variable is set to 'Y', a MERGE statement that references an archive-enabled table as its target returns an error. End of change

Tables with enforced row and column access controls

For information about how enabled row permissions and column masks affect the update and insert operations in the MERGE statement, see the INSERT and UPDATE statement information.

Example MERGE statements

FL 500For activities whose description has been changed, update the description in the RECORDS table. For new activities, insert into the RECORDS table. The RECORDS and ACTIVITIES tables both have ACTIVITY as a primary key. Start of change

MERGE INTO RECORDS AR
 USING (SELECT ACTIVITY, DESCRIPTION FROM ACTIVITIES) AC
 ON (AR.ACTIVITY = AC.ACTIVITY)
 WHEN MATCHED THEN
  UPDATE SET
  DESCRIPTION = AC.DESCRIPTION
 WHEN NOT MATCHED THEN
  INSERT
  (ACTIVITY, DESCRIPTION)
  VALUES (AC.ACTIVITY, AC.DESCRIPTION);

FL 500Using the SHIPMENT table, merge rows into the INVENTORY table: Increase the quantity by the part count in the SHIPMENT table for rows that match; else insert a row for the new part number into the inventory table. Start of change

MERGE INTO INVENTORY AS IN
 USING (SELECT PARTNO, DESCRIPTION, COUNT FROM SHIPMENT
 WHERE SHIPMENT.PARTNO IS NOT NULL) AS SH
 ON (IN.PARTNO = SH.PARTNO)
 WHEN MATCHED THEN
  UPDATE SET
   DESCRIPTION = SH.DESCRIPTION,
   QUANTITY = IN.QUANTITY + SH.COUNT
 WHEN NOT MATCHED THEN
  INSERT
  (PARTNO, DESCRIPTION, QUANTITY)
  VALUES (SH.PARTNO, SH.DESCRIPTION, SH.COUNT);

FL 500 Using the TRANSACTION table, merge rows into the ACCOUNT table: Update the balance from the set of transactions against an account ID, and insert new accounts from the consolidated transactions where they do not already exist. Start of change

MERGE INTO ACCOUNT AS A
 USING (SELECT ID, SUM(AMOUNT) SUM_AMOUNT FROM TRANSACTION
  GROUP BY ID) AS T
  ON A.ID = T.ID
 WHEN MATCHED THEN
  UPDATE SET
   BALANCE = A.BALANCE + T.SUM_AMOUNT
 WHEN NOT MATCHED THEN
  INSERT
  (ID, BALANCE)
   VALUES (T.ID, T.SUM_AMOUNT);

FL 500Using the TRANSACTION_LOG table, merge rows into the EMPLOYEE_FILE table: Update the phone and office with the latest TRANSACTION_LOG row based on the transaction time, and insert the latest new EMPLOYEE_FILE row where the row does not already exist. Start of change

MERGE INTO EMPLOYEE_FILE AS E
 USING (SELECT EMPID, PHONE, OFFICE
  FROM (SELECT EMPID, PHONE, OFFICE,
  ROW_NUMBER() OVER (PARTITION BY EMPID
  ORDER BY TRANSACTION_TIME DESC) RN
  FROM TRANSACTION_LOG) AS NT
  WHERE RN = 1) AS T
  ON E.EMPID = T.EMPID
 WHEN MATCHED THEN
  UPDATE SET
   (PHONE, OFFICE) =
   (T.PHONE, T.OFFICE)
 WHEN NOT MATCHED THEN
  INSERT
  (EMPID, PHONE, OFFICE)
  VALUES (T.EMPID, T.PHONE, T.OFFICE);

FL 500Update the list of activities organized by Group A in the RECORDS table. Delete all outdated activities and update the activities information (DESCRIPTION and DATE) in the RECORDS table if they have been changed. For new upcoming activities, insert into the RECORDS table. Signal an error if the date of the activity is not known. The date of the activities in the RECORDS table must be specified. Each group has an activities table. For example, ACTIVITIES_GROUPA contains all activities that group A organizes, and the RECORDS table contains all upcoming activities organized by different groups in a company. The RECORDS table has (GROUP, ACTIVITY) as the primary key, and DATE is not nullable. All activities tables have ACTIVITY as the primary key. The LAST_MODIFIED column in the RECORDS table is defined with CURRENT TIMESTAMP as the default value.
```
MERGE INTO RECORDS AR
 USING (SELECT ACTIVITY, DESCRIPTION, DATE, LAST_MODIFIED
  FROM ACTIVITIES_GROUPA) AC
  ON (AR.ACTIVITY = AC.ACTIVITY) AND AR.GROUP = 'A'
 WHEN MATCHED AND AC.DATE IS NULL THEN
  SIGNAL SQLSTATE '70001'
   SET MESSAGE_TEXT =
    AC.ACTIVITY CONCAT ' CANNOT BE MODIFIED. REASON: DATE IS NOT KNOWN'
 WHEN MATCHED AND AC.DATE < CURRENT DATE THEN
  DELETE
 WHEN MATCHED AND AR.LAST_MODIFIED < AC.LAST_MODIFIED THEN
  UPDATE SET
  (DESCRIPTION, DATE, LAST_MODIFIED) = (AC.DESCRIPTION, AC.DATE, DEFAULT)
 WHEN NOT MATCHED AND AC.DATE IS NULL THEN
  SIGNAL SQLSTATE '70002'
   SET MESSAGE_TEXT =
    AC.ACTIVITY CONCAT ' CANNOT BE INSERTED. REASON: DATE IS NOT KNOWN'
 WHEN NOT MATCHED AND AC.DATE >= CURRENT DATE THEN
  INSERT
   (GROUP, ACTIVITY, DESCRIPTION, DATE)
   VALUES ('A', AC.ACTIVITY, AC.DESCRIPTION, AC.DATE)
 ELSE IGNORE;
```

Update the descriptions for activities that exist in the RECORDS table. Otherwise, insert the activity and its description into the RECORDS table.

MERGE INTO RECORDS AR
  USING (VALUES (:hv_activity, :hv_description)
    FOR :hv_nrows ROWS)
    AS AC (ACTIVITY, DESCRIPTION)
  ON (AR.ACTIVITY = AC.ACTIVITY)
  WHEN MATCHED THEN UPDATE SET DESCRIPTION = AC.DESCRIPTION
  WHEN NOT MATCHED THEN INSERT (ACTIVITY, DESCRIPTION)
     VALUES (AC.ACTIVITY, AC.DESCRIPTION)
  NOT ATOMIC CONTINUE ON SQLEXCEPTION;

Use the transaction data to merge rows into the account table. Update the balance from the transaction data against an account ID and insert new accounts from the transaction data where the accounts do not already exist.

MERGE INTO ACCOUNT AS A
  USING (VALUES (:hv_id, :hv_amount) 
    FOR 3 ROWS)
    AS T (ID, AMOUNT)
  ON (A.ID = T.ID)
  WHEN MATCHED THEN UPDATE SET BALANCE = A.BALANCE + T.AMOUNT
  WHEN NOT MATCHED THEN INSERT (ID, BALANCE) 
      VALUES (T.ID, T.AMOUNT)
  NOT ATOMIC CONTINUE ON SQLEXCEPTION;

Update the list of activities that are organized by group A in the RECORDS table. Update the activities information (description and date when last modified) in the RECORDS table if the activities exist in the RECORDS table and are also organized by group A. Insert new activities into the RECORDS table.

-- hv_nrows = 3
-- hv_activity(1) = 'D'; hv_description(1) = 'Dance'; hv_date(1) = '03/01/07'
-- hv_activity(2) = 'S'; hv_description(2) = 'Singing'; hv_date(2) = '03/17/07'
-- hv_activity(3) = 'T'; hv_description(3) = 'Tai-chi'; hv_date(3) = '05/01/07'
-- hv_group = 'A';
-- note that hv_group is not an array. All 3 values contain the same values
MERGE INTO RECORDS AR
  USING (VALUES (:hv_activity, :hv_description, :hv_date, :hv_group)
     FOR :hv_nrows ROWS)
     AS AC (ACTIVITY, DESCRIPTION, DATE, GROUP)
  ON AR.ACTIVITY = AC.ACTIVITY AND AR.GROUP = AC.GROUP
  WHEN MATCHED 
  THEN UPDATE SET (DESCRIPTION, DATE, LAST_MODIFIED)
                  = (AC.DESCRIPTION, AC.DATE, CURRENT TIMESTAMP)
  WHEN NOT MATCHED
  THEN INSERT (GROUP, ACTIVITY, DESCRIPTION, DATE, LAST_MODIFIED)
     VALUES (AC.GROUP, AC.ACTIVITY, AC.DESCRIPTION, AC.DATE, CURRENT TIMESTAMP)
  NOT ATOMIC CONTINUE ON SQLEXCEPTION;

Use two arrays, CHARA and INTA, as input to a MERGE statement. Column COL2 is set to the cardinality of CHARA for matching rows, and COL2 is set to the cardinality of INTA for non-matching rows. Start of change

CREATE TYPE INTARRAY AS INTEGER ARRAY[6];
CREATE TYPE CHARARRAY AS CHAR(20) ARRAY[7];
CREATE VARIABLE INTA AS INTARRAY;
CREATE VARIABLE CHARA AS CHARARRAY;
CREATE VARIABLE SI INT;
SET CHARA = ARRAY['a', 'b', 'c'];
SET INTA = ARRAY [1, 2, 3, 4, 5];
CREATE TABLE T1 (COL1 CHAR(7), COL2 INT);
INSERT INTO T1 VALUES ('abc', 10);
MERGE INTO T1 AS A
 USING TABLE (VALUES ('rsk', 3 ) ) AS T (ID, AMOUNT)
 ON A.COL1 = T.ID
 WHEN MATCHED
  THEN UPDATE SET COL2 = CARDINALITY(CHARA)
 WHEN NOT MATCHED
  THEN INSERT (COL1, COL2 ) VALUES (T.ID, CARDINALITY(INTA));