Start of change

MERGE

The MERGE statement updates a target (a table or view) using data from a source (result of a table reference). Rows in the target that match the input data may be updated or deleted as specified, and rows that do not exist in the target may be inserted as specified. Updating, deleting, or inserting a row in 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 the view.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared. It is not allowed in a REXX procedure.

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:

  • Administrative authority
  • If an insert operation is specified:
    • The INSERT privilege on the table or view, and
    • The system authority *EXECUTE on the library containing the table or view
  • If a delete operation is specified:
    • The DELETE privilege on the table or view, and
    • The system authority *EXECUTE on the library containing the table or view
  • If an update operation is specified:
    • The UPDATE privilege on the table or view or
    • Start of changeThe UPDATE privilege on each column to be updated; andEnd of change
    • The system authority *EXECUTE on the library containing the table or view

If search-condition, insert-operation, or assignment-clause includes a fullselect, the privileges held by the authorization ID of the statement must also include at least one of the following:

  • For each table or view identified in the fullselect:
    • The SELECT privilege on the table or view, and
    • The system authority *EXECUTE on the library containing the table or view
  • Administrative authority

If table-reference contains any query that references a column of a table or view, the privileges held by the authorization ID of the statement must also include at least one of the following:

  • For each table or view identified in the fullselect:
    • The SELECT privilege on the table or view, and
    • The system authority *EXECUTE on the library containing the table or view
  • Administrative authority

For information about the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Table or View.

Syntax

Read syntax diagramSkip visual syntax diagram
>>-MERGE INTO--+-table-name-+--+--------------------+----------->
               '-view-name--'  '-correlation-clause-'   

>--USING--table-reference--ON--search-condition----------------->

   .------------------------------------------------------.   
   V                                                      |   
>----WHEN--matching-condition--THEN--+-update-operation-+-+----->
                                     +-delete-operation-+     
                                     +-insert-operation-+     
                                     '-signal-statement-'     

   .-ELSE IGNORE-.   
>--+-------------+---------------------------------------------->

   .-ATOMIC-----------------------------------.   
>--+------------------------------------------+----------------->
   '-NOT ATOMIC--+-STOP ON SQLEXCEPTION-----+-'   
                 '-CONTINUE ON SQLEXCEPTION-'     

   .-----------------------------------------.   
   V                                         |   
>----+-------------------------------------+-+-----------------><
     +-isolation-clause--------------------+     
     '-concurrent-access-resolution-clause-'     

correlation-clause

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

matching-condition

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

update-operation

|--UPDATE--+-------------------------+--SET--assignment-clause--|
           +-OVERRIDING SYSTEM VALUE-+                           
           '-OVERRIDING USER VALUE---'                           

delete-operation

|--DELETE-------------------------------------------------------|

insert-operation

|--INSERT--+-----------------------+--+-------------------------+-->
           |    .-,-----------.    |  +-OVERRIDING SYSTEM VALUE-+   
           |    V             |    |  '-OVERRIDING USER VALUE---'   
           '-(----column-name-+--)-'                                

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

assignment-clause

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

isolation–clause

|--WITH--+-NC-+-------------------------------------------------|
         +-UR-+   
         +-CS-+   
         +-RS-+   
         '-RR-'   

Description

table-name or view-name
Identifies the target of the update, insert, and delete 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 view of a catalog table, Start of changea read-only view, or a non-deletable view independent of any INSTEAD OF triggers defined for it.End of change
AS correlation-name
Can be used within search-condition, matching-condition, or on the right side of an assignment-clause to designate the target table or view. The correlation-name is used to qualify references to the columns of the table or view. When a correlation-name is specified, column-names can also be specified to give names to the columns of the 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. For more information, see Correlation names
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.
ON search-condition
Start of changeSpecifies the predicates used to determine whether a row from source-table matches rows in the target table.End of change

Each column-name in the search-condition, other than in a subquery, must name a column of the target table or view or the table-reference. When the search condition includes a subquery in which the same table is the base object of both the merge and the subquery, the subquery is completely evaluated before any rows are updated or inserted.

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 where the result of the search-condition is not true, the specified insert operation is performed.

The search-condition cannot contain a quantified subquery, IN predicate with a subselect, or EXISTS subquery. It can contain basic predicate subqueries or scalar-fullselects. It cannot contain expressions that use aggregate functions or non-deterministic scalar functions.

WHEN matching-condition
Specifies the condition under which the Start of changeupdate-operationEnd of change, delete-operation, insert-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.

The search-condition must not include a subquery in an EXISTS or IN predicate.

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.

The search-condition must not include a subquery in an EXISTS or IN predicate.

Start of changeTHEN End of change
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.
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 target table or view. The column-name must not identify a view column derived from a scalar function, constant, or expression. A column name must not be specified more than once.

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.

ROW
Identifies all the columns of the target table or view except for columns defined with the hidden attribute. If a view is specified, none of the columns of the view may be derived from a scalar function, constant, or expression.
The number of expressions, NULLs, and DEFAULTs (or the number of result columns from a row-fullselect) must match the number of columns in the row.
ROW may not be specified for a view that contains a view column derived from the same column as another column of the view, because both columns cannot be updated in the same UPDATE.
expression
Indicates the new value of the column. The expression must not include an aggregate function Start of changeexcept when it occurs within a scalar fullselectEnd of change.

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

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

Start of changeWhen extended indicator variables are enabled, the extended indicator variable values of DEFAULT (-5) or UNASSIGNED (-7) must not be used if expression is more complex than the following references:
  • A single column of the source table
  • A single host variable
End of change
DEFAULT
Start of changeSpecifies that the default value is assigned to the column. DEFAULT can be specified only for columns that have a default value. For more information about default values, see the description of the DEFAULT clause in CREATE TABLE.

DEFAULT must be specified for a column that was defined as GENERATED ALWAYS unless OVERRIDING USER VALUE is specified to indicate that any user-specified value will be ignored and a unique system-generated value will be used. 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.
delete-operation
Specifies the delete operation to be executed for the rows where the matching-condition evaluates to true.
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.
Start of changeThe number of values in the row value expression must equal the number of names in the implicit or explicit 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.End of change
(column-name,…)
Specifies the columns for which insert values are provided. Each name must be a name that identifies a column of the table or view. The same column must not be identified more than once. If extended indicator variables 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 such 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.

Omission of the column list is an implicit specification of a list in which every column of the table or view is identified in left-to-right order. Any columns defined with the hidden attribute are omitted. 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
Start of changeSpecifies a new row to be inserted.End of change

Start of changeEach variable in the clause must identify a variable that is declared in accordance with the rules for declaring variables. A host structure cannot be used. For further information about variables, see References to host variables.End of change

expression
An expression of the type described in Expressions, that does not include an aggregate function or column name.
Start of changeWhen extended indicator variables are enabled, the extended indicator variable values of DEFAULT (-5) or UNASSIGNED (-7) must not be used if expression is more complex than the following references:
  • A single column of the source table
  • A single host variable
  • A host variable being explicitly cast
End of change
DEFAULT
Start of changeSpecifies that the default value is assigned to the column. DEFAULT can be specified only for columns that have a default value. For more information about default values, see the description of the DEFAULT clause in CREATE TABLE.

DEFAULT must be specified for a column that was defined as GENERATED ALWAYS unless OVERRIDING USER VALUE is specified to indicate that any user-specified value will be ignored and a unique system-generated value will be used. A valid value can be specified for a column that was defined as GENERATED BY DEFAULT.

End of change
NULL
Specifies the value for a column is the null value. NULL should only be specified for nullable columns.
OVERRIDING SYSTEM VALUE or OVERRIDING USER VALUE
Specifies whether system generated values or user-specified values for a ROWID, identity, or row change timestamp column are used. If OVERRIDING SYSTEM VALUE is specified, the implicit or explicit list of columns for the INSERT or the SET clause of the UPDATE must contain a column defined as GENERATED ALWAYS. If OVERRIDING USER VALUE is specified, the implicit or explicit list of columns for the INSERT or the SET clause of the UPDATE must contain a column defined as either GENERATED ALWAYS or GENERATED BY DEFAULT.
OVERRIDING SYSTEM VALUE
Specifies that the value specified in the VALUES or SET clause for a column that is defined as GENERATED ALWAYS is used. A system-generated value is not used.
OVERRIDING USER VALUE
Specifies that the value specified in the VALUES or SET clause for a column that is defined as either GENERATED ALWAYS or GENERATED BY DEFAULT is ignored. Instead, a system-generated value is used, overriding the user-specified value.

If neither OVERRIDING SYSTEM VALUE nor OVERRIDING USER VALUE is specified:

  • A value cannot be specified for a ROWID, identity, or row change timestamp column that is defined as GENERATED ALWAYS.
  • A value can be specified for a ROWID, identity, or row change timestamp column that is defined as GENERATED BY DEFAULT. If a value is specified that value is assigned to the column. However, a value can be assigned to a ROWID column defined BY DEFAULT only if the specified value is a valid row ID value that was previously generated by DB2® for z/OS® or DB2 for i. When a value is inserted or updated for an identity or row change timestamp column defined BY DEFAULT, the database manager does not verify that the specified value is a unique value for the column unless the identity or row change timestamp column is the sole key in a unique constraint or unique index. Without a unique constraint or unique index, the database manager can guarantee unique values only among the set of system-generated values as long as NO CYCLE is in effect.

    If a value is not specified the database manager generates a new value.

signal-statement
Specifies the SIGNAL statement that is to be executed to return an error when the matching-condition evaluates to true. See SIGNAL.
ELSE IGNORE
Start of changeSpecifies that no action is to be taken when the matching-condition for all WHEN clauses is false for a row in the USING table-reference. No action is taken in this case whether or not ELSE IGNORE is specified. End of change
ATOMIC or NOT ATOMIC
Specifies how to handle errors
ATOMIC
Specifies that if an error occurs during a Start of changeupdate-operationEnd of change, delete-operation, or Start of changeinsert-operationEnd of change, the entire MERGE statement is rolled back.
NOT ATOMIC
Specifies that if an error occurs during a Start of changeupdate-operationEnd of change, delete-operation, or Start of changeinsert-operationEnd of change, only that Start of changeupdate-operationEnd of change, delete-operation, or Start of changeinsert-operationEnd of change is rolled back.
STOP ON SQL EXCEPTION
Specifies that if an error occurs during a Start of changeupdate-operationEnd of change, delete-operation, or Start of changeinsert-operationEnd of change, the processing of the MERGE statement stops.
CONTINUE ON SQL EXCEPTION
Specifies that if an error occurs during a Start of changeupdate-operationEnd of change, delete-operation, or Start of changeinsert-operationEnd of change, the processing of the MERGE statement continues.
Start of changeisolation-clause End of change
Start of changeSpecifies the isolation level to be used for this statement.
WITH
Introduces the isolation level, which may be one of:
  • RR Repeatable read
  • RS Read stability
  • CS Cursor stability
  • UR Uncommitted read
  • NC No commit
If isolation-clause is not specified the default isolation is used. See isolation-clause for a description of how the default is determined.End of change
Start of changeconcurrent-access-resolution-clauseEnd of change
Start of changeSpecifies the concurrent access resolution to use for the select statement. For more information, see concurrent-access-resolution-clause.End of change

MERGE Rules

  • More than one Start of changeupdate-operationEnd of change, delete-operation, Start of changeinsert-operationEnd of change, 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 hierarchy) as a target of an UPDATE, DELETE, INSERT, or MERGE statement.
For other rules that affect the update, insert, or delete portion of the MERGE statement, see the "Rules" section of the corresponding statement description.

Start of changeExtended indicator variable usage: If enabled, indicator variable values other than positive values and 0 (zero) through -7 must not be set. The DEFAULT and UNASSIGNED extended indicator variable values must not appear in contexts where they are not supported.End of change

Start of changeExtended indicator variables: In an insert portion of the MERGE statement, the extended indicator value of UNASSIGNED has the effect of setting the column to its default value.End of change

Start of changeMERGE restriction: If the target table of the MERGE statement has triggers or is the parent in a referential integrity constraint, the Start of changeupdate-operationEnd of change or Start of changeinsert-operationEnd of change must not contain a global variable, a function, or a subselect.End of change

Notes

Start of changeLogical order of processing: For a NOT ATOMIC MERGE statement, 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, will fire any triggers (including statement level triggers) when the update of the row is performed. Thus, if 5 rows are updated, any update triggers (including statement level update triggers) will be fired 5 times.End of change

Start of changeFor an ATOMIC MERGE statement, the source rows are processed as if a set of rows is processed by each WHEN clause. Thus, if 5 rows are updated, any row level update triggers will be fired 5 times, but n statement level update triggers will be fired, 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 ATOMIC MERGE, the logical order of processing is:
  1. Determine the set of rows 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.
  2. Use the ON clause to classify these rows as either MATCHED or NOT MATCHED.
  3. Evaluate any matching-condition in the WHEN clauses.
  4. Evaluate any expression in any assignment-clause and insert-operation.
  5. Execute each signal-statement.
  6. Apply each Start of changeupdate-operationEnd of change, delete-operation, or Start of changeinsert-operationEnd of change to the applicable rows in the order of specification. The triggers activated by each Start of changeupdate-operationEnd of change, delete-operation, or Start of changeinsert-operationEnd of change are executed. Statement level triggers are activated even if no rows satisfy the Start of changeupdate-operationEnd of change, delete-operation, or Start of changeinsert-operationEnd of change. Each Start of changeupdate-operationEnd of change, delete-operation, or Start of changeinsert-operationEnd of change can affect the triggers of each subsequent Start of changeupdate-operationEnd of change, delete-operation, or Start of changeinsert-operationEnd of change.
End of change

Number of rows updated: After executing a MERGE statement, the ROW_COUNT statement information item in the SQL Diagnostics Area (or SQLERRD(3) of the SQLCA) is the number of rows operated on by the MERGE statement, excluding rows identified by the ELSE IGNORE clause. The ROW_COUNT item and SQLERRD(3) does not include the number of rows that were operated on as a result of triggers. The value in the DB2_ROW_COUNT_SECONDARY statement information item (or SQLERRD(5) of the SQLCA) includes the number of these rows.

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

GET DIAGNOSTICS considerations: If a MERGE statement completes with one or more errors, the GET DIAGNOSTICS statement can be used after the MERGE statement to check which input row(s) failed. The GET DIAGNOSTICS statement-information-item, NUMBER, indicates the number of conditions (errors of warnings) detected by execution of the MERGE statement. For each condition, the GET DIAGNOSTICS condition-information-item, DB2_ROW_NUMBER, indicates the input source row that caused an error.

Inserted row cannot also be updated: No attempt is made to update a row in the target that did not already exist before the MERGE statement was executed; that is, there are no updates of rows that were inserted by the MERGE statement.

Concurrent row changes in MERGE target: MERGE processing determines affected rows in the MERGE target before performing any Start of changeupdate-operationsEnd of change, delete-operations, or Start of changeinsert-operationsEnd of change. Unless using a restrictive isolation level such as repeatable read, concurrent processes could insert or modify rows in the MERGE target between the time when the set of affected target rows is determined and when a specific row Start of changeupdate-operationEnd of change, delete-operation, or Start of changeinsert-operationEnd of change is processed. Such concurrent activity could produce an error. For example, MERGE processing could determine that a source row does not exist in the target where a column value in the target has a unique key constraint. Before the MERGE attempts to insert a new row based on the source data, a concurrent process could insert a row with the same key value. This would cause a duplicate key error when the MERGE processing attempts to insert its row.

Locking: If COMMIT(*RR), COMMIT(*ALL), COMMIT(*CS), or COMMIT(*CHG) is specified, one or more exclusive locks are acquired during the execution of a successful MERGE statement. Until the locks are released by a commit or rollback operation, an inserted or updated row can only be accessed by:

  • The application process that performed the insert or update
  • Another application process using COMMIT(*NONE) or COMMIT(*CHG) through a read-only operation

The locks can prevent other application processes from performing operations on the table. For further information about locking, see the description of the COMMIT, ROLLBACK, and LOCK TABLE statements. Also, see Isolation level and Database Programming.

A maximum of 500 000 000 rows can be acquired in any single MERGE statement when COMMIT(*RR), COMMIT(*ALL), COMMIT(*CS), or COMMIT(*CHG) was specified. The number of row locks includes any rows inserted, updated, or deleted in the MERGE target and any rows inserted, updated, or deleted under the same commitment definition as a result of a trigger. The number of row locks also includes source rows referenced by the USING table-reference is COMMIT(*ALL) is specified.

NOT ATOMIC processing: When NOT ATOMIC is specified, the rows of source data are processed separately. Any reference to special registers (such as CURRENT TIMESTAMP) in the MERGE statement are evaluated as each row or source data is processed. Statement level triggers are activated as each row of source data is processed.

If an error occurs during the operation for a row of source data, the row being processed at the time of the error is not inserted, updated, or deleted. Processing of an individual row is an atomic operation. Any other changes previously made during the processing of the MERGE statement are not rolled back. If CONTINUE ON EXCEPTION is specified, execution continues with the next row to be processed.

Examples

Example 1: For activities whose description has changed, update the description in the archive table. For new activities, insert into the archive table. The archive and activities tables both have activity as a primary key.
MERGE INTO archive 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)
Example 2: Using the shipment table, merge rows into the inventory table, increasing the quantity by part count in the shipment table for rows that match; else insert the new partno into the inventory table.
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)
Example 3: Using the transaction table, merge rows into the account table, updating the balance from the set of transactions against an account ID and inserting new accounts from the consolidated transactions where they do not already exist.
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)
Example 4: Using the transaction_log table, merge rows into the employee_file table, updating the phone and office columns with the latest transaction_log row based on the transaction time, and inserting a new employee_file row where the row does not already exist.
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)    
Example 5: Using dynamically supplied values for an employee row, update the master employee table if the data corresponds to an existing employee, or insert the row if the data is for a new employee. The following example is a fragment of code from a C program.Start of change
hv1 =
"MERGE INTO employee AS t
   USING (VALUES(CAST(? AS CHAR(6)), CAST(? AS VARCHAR(12)),
                      CAST(? AS CHAR(1)), CAST(? AS VARCHAR(15)),
                      CAST(? AS SMALLINT), CAST(? AS INTEGER)))
          s (empno, firstnme, midinit, lastname, edlevel, salary)
   ON t.empno = s.empno
   WHEN MATCHED THEN
     UPDATE SET salary = s.salary
   WHEN NOT MATCHED THEN 
     INSERT (empno, firstnme, midinit, lastname, edlevel, salary)
            VALUES (s.empno, s.firstnme, s.midinit, s.lastname, s.edlevel,
                    s.salary)";
EXEC SQL PREPARE s1 FROM :hv1;
EXEC SQL EXECUTE s1 USING :hv2, :hv3, :hv4, :hv5, :hv6, :hv7;
End of change
Example 6: Update the list of activities organized by Group A in the archive table. Delete all outdated activities and update the activities information (description and date) in the archive table if they have been changed. For new upcoming activities, insert into the archive. Signal an error if the data of the activity is not known. The date of the activities in the archive table must be specified. Each group has an activities table. For example, activities_groupA contains all activities that they organize, and the archive table contains all upcoming activities organized by different groups in a company. The archive table has (group, activity) as the primary key, and data is not nullable. All activities tables have activity as the primary key. The last_modified column in the archive is defined with CURRENT TIMESTAMP as the default value.
MERGE INTO archive 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 = 'Activity cannot be modified.  Reason: date is not known'
  WHEN MATCHED and ac.date < CURRENT DATE THEN 
    DELETE
  WHEN MATCHED AND as.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 = 'Activity 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    
End of change