DB2 Version 9.7 for Linux, UNIX, and Windows
DB2 Version 9.7 for Linux, UNIX, and Windows
The MERGE statement updates a target (a table or view, or the underlying tables or views of a fullselect) using data from a source (result of a table reference). Rows in the target that match the source can be deleted or updated as specified, and rows that do not exist in the target can be inserted. Updating, deleting or inserting a row in a view updates, deletes or inserts the row in the tables on which the view is based.
This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared.
If an expression that refers to a function is specified, the privilege set must include any authority that is necessary to execute the function.
>>-MERGE INTO--+-table-name-------+-----------------------------> +-view-name--------+ '-(--fullselect--)-' >--+------------------------+--USING--table-reference-----------> '-| correlation-clause |-' >--ON--search-condition-----------------------------------------> .--------------------------------------------------------------------. V | >----WHEN--| matching-condition |--THEN--+-| modification-operation |-+-+--> '-signal-statement-----------' .-ELSE IGNORE-. >--+-------------+--+--------------+--------------------------->< '-WITH--+-RR-+-' +-RS-+ +-CS-+ '-UR-' correlation-clause .-AS-. |--+----+--correlation-name--+-----------------------+----------| | .-,-----------. | | V | | '-(----column-name-+--)-' matching-condition |--+-----+--MATCHED--+-----------------------+------------------| '-NOT-' '-AND--search-condition-' modification-operation |--+-UPDATE SET--| assignment-clause |-+------------------------| +-DELETE----------------------------+ '-| insert-operation |--------------' assignment-clause .-,----------------------------------------------------------------. V | |----+-column-name-- = --+-expression-+-----------------------------+-+--| | +-DEFAULT----+ | | '-NULL-------' | | .-,-----------. .-,------------------. | | V | V (1) | | '-(----column-name-+--)-- = --(--+---+-expression-----+-+-+--)-' | +-DEFAULT--------+ | | '-NULL-----------' | | (2) | '-row-fullselect---------' insert-operation |--INSERT--+-----------------------+----------------------------> | .-,-----------. | | V | | '-(----column-name-+--)-' >--VALUES--+-+-expression-+-----------+-------------------------| | +-DEFAULT----+ | | '-NULL-------' | | .-,--------------. | | V | | '-(----+-expression-+-+--)-' +-DEFAULT----+ '-NULL-------'
If the target of the merge operation is a fullselect, the fullselect must be updatable, deletable, or insertable as defined in the "Updatable views", "Deletable views", or "Insertable views" Notes items in the description of the CREATE VIEW statement.
You cannot use a nickname (a reference to a remote, federated table) as the target table.
If the search-condition is false or unknown for every row in table-reference, a warning is returned (SQLSTATE 02000).
A view column derived from the same column as another column of the view can be updated, but both columns cannot be updated in the same MERGE statement (SQLSTATE 42701).
An expression can contain references to columns of the table-name or view-name. For each row that is updated, the value of such a column in an expression is the value of the column in the row before the row is updated.
If expression is a reference to a single column of the source table, the source table column value may have been specified with an extended indicator variable value. The effects of such indicator variables apply to the corresponding target columns of the assignment-clause.
If expression is a single host variable, or a host variable being explicitly cast, the host variable can include an indicator variable that is enabled for extended indicator variables.
When extended indicator variables are enabled, the extended indicator variable values of default (-5) or unassigned (-7) must not be used (SQLSTATE 22539) if either of the following statements is true:
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.
A row-fullselect can contain references to columns of the target table of the MERGE statement. For each row that is updated, the value of such a column in an expression is the value of the column in the row before the row is updated. An error is returned if there is more than one row in the result (SQLSTATE 21000).
The number of values for the row in the row value expression must equal the number of names in the insert column list. The first value is inserted in the first column in the list, the second value in the second column, and so on.
If the object of the operation is a view with such columns, a list of column names must be specified, and the list must not identify these columns.
Omission of the column list is an implicit specification of a list in which every column of the table (that is not defined as implicitly hidden) or view is identified in left-to-right order. This list is established when the statement is prepared, and therefore does not include columns that were added to a table after the statement was prepared.
If expression is a reference to a single column of the source table, the source table column value may have been specified with an extended indicator variable value. The effects of such indicator variables apply to the corresponding target columns of the insert-operation.
If expression is a single host variable, or a host variable being explicitly cast, the host variable can include an indicator variable (or in the case of a host structure, an indicator array) that is enabled for extended indicator variables.
When extended indicator variables are enabled, the extended indicator variable values of default (-5) or unassigned (-7) must not be used (SQLSTATE 22539) if either of the following statements is true:
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.
The session authorization ID must also have been granted a security label for write access for the security policy if an implicit value is used for a DB2SECURITYLABEL column (SQLSTATE 23523), which can happen when:
The session authorization ID must also have been granted a security label for write access for the security policy if an implicit value is used for a DB2SECURITYLABEL column (SQLSTATE 23523), which can happen when:
If a target column is not updatable (for example, a column in a view that is defined as an expression), then it must be assigned the extended indicator variable-based value of unassigned (SQLSTATE 42808).
If the target column is a column defined as GENERATED ALWAYS, then it must be assigned the DEFAULT keyword, or the extended indicator variable-based values of default or unassigned (SQLSTATE 428C9).
The assignment-clause must not assign all target columns to an extended indicator variable-based value of unassigned (SQLSTATE 22540).
If a target column is not updatable, then it must be assigned the extended indicator variable-based value of unassigned (SQLSTATE 42808), unless it is a column defined as GENERATED ALWAYS. If the target column is a column defined as GENERATED ALWAYS, then it must be assigned the DEFAULT keyword, or the extended indicator variable-based values of default or unassigned (SQLSTATE 428C9).
For other rules that affect the update, insert, or delete operation portion of the MERGE statement, see the "Rules" section of the corresponding statement description.
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', 18, 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 =
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