
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.
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.
The privileges held by the authorization ID of the statement must include at least one of the following:
The UPDATE privilege on each column to be updated;
and
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:
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 information about the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Table or View.
>>-MERGE INTO--+-table-name-+--+--------------------+-----------> '-view-name--' '-correlation-clause-' >--USING--table-reference--ON--search-condition-----------------> .------------------------------------------------------------. V | >----WHEN--matching-condition--THEN--+-modification-operation-+-+--> '-signal-statement-------' .-ELSE IGNORE-. >--+-------------+----------------------------------------------> .-ATOMIC-----------------------------------. >--+------------------------------------------+---------------->< '-NOT ATOMIC--+-STOP ON SQLEXCEPTION-----+-' '-CONTINUE ON SQLEXCEPTION-' correlation-clause .-AS-. |--+----+--correlation-name--+-----------------------+----------| | .-,-----------. | | V | | '-(----column-name-+--)-' matching-condition |--+-----+--MATCHED--+-----------------------+------------------| '-NOT-' '-AND--search-condition-' modification-operation |--+-UPDATE--+-------------------------+--SET--assignment-clause-+--| | +-OVERRIDING SYSTEM VALUE-+ | | '-OVERRIDING USER VALUE---' | +-DELETE------------------------------------------------------+ '-insert-operation--------------------------------------------' assignment-clause .-,-----------------------------------------------------------. V | |--+---+-column-name--=--+-expression-+--------------------------+-+-+--| | | +-DEFAULT----+ | | | | '-NULL-------' | | | | .-,------------. .-,--------------. | | | | V | V | | | | '-(---- column-name-+--)--=--(--+---+-expression-+-+-+--)-' | | | +-DEFAULT----+ | | | | '-NULL-------' | | | '-row-fullselect-----' | | .-,--------------. | | V | | '-ROW--=--(--+---+-expression-+-+-+--)----------------------------' | +-DEFAULT----+ | | '-NULL-------' | '-row-fullselect-----' insert-operation |--INSERT--+-----------------------+--+-------------------------+--> | .-,-----------. | +-OVERRIDING SYSTEM VALUE-+ | V | | '-OVERRIDING USER VALUE---' '-(----column-name-+--)-' >--VALUES--+-+-expression-+-----------+-------------------------| | +-DEFAULT----+ | | '-NULL-------' | | .-,--------------. | | V | | '-(----+-expression-+-+--)-' +-DEFAULT----+ '-NULL-------'
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.
The search-condition must not include a subquery in an EXISTS or IN predicate.
The search-condition must not include a subquery in an EXISTS or IN predicate.
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.
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.
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.
Specifies a new row to be inserted.
Each variable in the clause must identify a host structure or variable that is declared in accordance with the rules for declaring host structures and variables. In the operational form of the statement, a reference to a host structure is replaced by a reference to each of its variables. For further information about variables and structures, see References to host variables and Host structures.
The number of values for the row in the VALUES clause must equal the number of names in the implicit or explicit column list. The first value is inserted in the first column in the list, the second value in the second column, and so on.
DEFAULT must be specified for a ROWID or an identity 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 inserted.
If neither OVERRIDING SYSTEM VALUE nor OVERRIDING USER VALUE is specified:
If a value is not specified the database manager generates a new value.
Specifies 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. 
Logical 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.
For 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: 
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 modification-operation(s). 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 modification-operation 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 insert, update, or delete. Until the locks are released by a commit or rollback operation, an inserted or updated row can only be accessed by:
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.
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)
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)
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)
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)
hv1 =
"MERGE INTO employee AS t
USING TABLE(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 '000420', 'SERGE', 'K', 'FIELDING', 198, 39580;
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
