This document covers the following topics:
For an explanation of the symbols used in the syntax diagram, see Syntax Symbols.
Belongs to Function Group: Database Access and Update
See also MERGE - SQL in the Natural for Db2 part of the Database Management System Interfaces documentation.
The MERGE
statement updates a table using the specified
input data. Rows in the target table that match the input data are updated as
specified, and rows that do not exist in the target table are inserted.
The MERGE
statement belongs to the
SQL Extended Set.
This statement is available only with Natural for Db2.
Syntax Element | Description |
---|---|
MERGE
INTO |
MERGE INTO Clause:
|
table-name |
Table Name:
Identifies the target of the See table-name specification. |
[AS]
correlation-name
|
[AS] correlation-name
Clause:
Specifies an alternate name for the target table. The alternate name can be used as qualifier when referencing columns of the intermediate result table. |
include-columns |
Include Columns Clause:
Specifies a set of columns that are included, along with the columns of the
target table, in the result table of the For further details, see include-columns. |
USING
source-table |
USING source-table
Clause:
Specifies the values for the row data to merge into the target table. See
|
ON
search-condition |
ON search-condition
Clause:
Specifies join conditions between the |
WHEN
matching-condition |
WHEN matching-condition
Clause:
Specifies the condition for which to perform the modification operation defined
in the following |
THEN
modification-operation |
THEN modification-operation
Clause:
Specifies the operation to perform on the matches of the condition defined in
the preceding |
ELSE
IGNORE |
ELSE IGNORE Clause:
Specifies that no action is taken on the source columns that do not match the
condition specified in the |
NOT ATOMIC CONTINUE ON
SQLEXCEPTION |
NOT ATOMIC CONTINUE ON SQLEXCEPTION Clause:
Specifies whether merge processing continues in case an error occurred during processing one row of a set of source rows. |
QUERYNO
integer |
QUERYNO integer
Clause:
Specifies the number for this SQL statement that is used in
|
table-reference | ||||
(VALUES |
) | |||
[AS ] correlation-name
(column-name,...)
|
Syntax Element | Description |
---|---|
table-reference |
Specifies the source table to merge into the target table. |
VALUES |
VALUES introduces the specification of values
for the row data to merge into the target table.
|
values-single-row |
Specifies a single row of source data. See values-single-row. |
values-multiple-row |
Specifies multiple rows of source data. See values-multiple-row. |
[AS]
correlation-name |
Specifies a correlation name for the source table. |
column-name |
Specifies a column name to associate the input data to the
UPDATE SET
assignment-clause clause for an
UPDATE operation or the VALUES clause for an
INSERT operation.
|
[NOT ] MATCHED [AND
search-condition]
|
update-operation )
|
||||
DELETE |
||||
signal-operation |
||||
insert-operation |
SIGNAL
SQLSTATE
[VALUE] sqlstate
[SET
MESSAGE_TEXT
= scalar-expression ]
|
( | ,...) |
Syntax Element | Description |
---|---|
expression |
Scalar Expression:
Specifies a scalar expression as described in Scalar Expressions. |
NULL |
NULL Value:
Specifies the null value. |
FOR |
host-variable |
ROWS |
|||||||||||||||
( | ,...) |
Syntax Element | Description |
---|---|
expression |
Scalar Expression:
Specifies a scalar expression as described in Scalar Expressions. |
host-variable-array |
Host-Variable Array:
Specifies a host variable array or an index range of an array. If
|
NULL |
NULL Value:
Specifies the null value. |
FOR ...
ROWS |
Number of Rows to Merge:
This clause specifies the number of rows to merge.
|
UPDATE SET
assignment-clause |
Syntax Element | Description |
---|---|
UPDATE
SET |
Specifies the UPDATE operation to be performed
when the search-condition evaluates to
true.
See |
column-name = | expression | ,... | |||||
DEFAULT |
|||||||
NULL |
|||||||
(column-name,...) = ( | expression | ,...) | |||||
DEFAULT |
|||||||
NULL |
INSERT [(column-name,...)] VALUES (
|
,...) |
Update the inventory at a car dealership. Add new car model to the inventory or update information about existing car model that is already in the inventory.
DEFINE DATA LOCAL 01 #MODEL (A20) 01 #DELTA (I4) END-DEFINE * Setup input host variables ASSIGN #MODEL = ‘Grand Turbo’ ASSIGN #DELTA := 5 * Insert/Update into INVENTORY table MERGE INTO CDS-INVENTORY T USING (VALUES (:#MODEL, :#DELTA)) AS S(MODEL, DELTA) ON T.MODEL = S.MODEL WHEN MATCHED THEN UPDATE SET T.QUANTITY = T.QUANTITY + S.DELTA WHEN NOT MATCHED THEN INSERT VALUES (S.MODEL, S.DELTA) END TRANSACTION END
Update the inventory at a car dealership. Add new car models to the inventory and update information about car models that are already in the inventory. Input comes from Natural arrays. Array specific code is shown in bold face type.
OPTIONS DB2ARRY ON DEFINE DATA LOCAL 01 #MODEL_ARR (A20/1:20) 01 #DELTA_ARR (I4/1:20)) 01 #ROW-COUNT (I4) 01 #NUM-ERRORS (I4) 01 #SQLCODE (I4) 01 #SQLSTATE (A5) 01 #ROW-NUM (I4) END-DEFINE * Setup input host variables ASSIGN #MODEL_ARR(1) = ‘Grand Turbo’ ASSIGN #DELTA_ARR(1) := 5 ASSIGN #MODEL_ARR(2) = ‘Blue Car’ ASSIGN #DELTA_ARR(2) := 3 . . . * Insert/Update into INVENTORY table CALLNAT 'NDBNOERR' MERGE INTO CDS-INVENTORY T USING (VALUES (:#MODEL_ARR(*), :#DELTA_ARR(*)) FOR 20 ROWS) AS S(MODEL, DELTA) ON T.MODEL = S.MODEL WHEN MATCHED THEN UPDATE SET T.QUANTITY = T.QUANTITY + S.DELTA WHEN NOT MATCHED THEN INSERT VALUES (S.MODEL, S.DELTA) NOT ATOMIC CONTINUE ON SQLEXCEPTION * Check outcome of MERGE PROCESS SQL SYSIBM-SYSDUMMY1 <<GET DIAGNOSTICS :#ROW-COUNT = ROW_COUNT ,:#NUM-ERRORS = NUMBER>> WRITE 'Number of rows merged :' #ROW-COUNT / 'NUMBER OF ERRORS :' #NUM-ERRORS IF #NUM-ERRORS > 0 FOR #I = 1 TO #NUM-ERRORS PROCESS SQL SYSIBM-SYSDUMMY1 <<GET DIAGNOSTICS CONDITION :#I :#SQLCODE = DB2_RETURNED_SQLCODE, :#SQLSTATE = RETURNED_SQLSTATE, :#ROW_NUM = DB2_ROW_NUMBER>> PRINT 'DB2_RETURNED_SQLCODE:' #SQLCODE 'RETURNED_SQLSTATE:' #SQLSTATE 'DB2_ROW_NUMBER:' #ROW_NUM (EM=99Z) END-FOR END-IF END TRANSACTION END
Merge sales data from the MSALES
table into the
MPRODUCT
table. Demonstrate the MERGE
operation with
DELETE
, UPDATE
, INSERT
, and
SIGNAL
statements.
DEFINE DATA LOCAL USING DEMSQLCA LOCAL 1 V1 VIEW OF MPRODUCT 2 ID 2 NAME 2 INVENTORY 1 #M_TEXT (A10) INIT <'Oversold: '> END-DEFINE ... MERGE INTO MPRODUCT AS T USING (SELECT MSALES.ID ,SUM(MSALES.SOLD) AS SOLD, MAX(MCATALOG.NAME) AS NAME FROM MSALES, MCATALOG WHERE MSALES.ID = MCATALOG.ID GROUP BY MSALES.ID) AS S (ID,SOLD,NAME) ON S.ID = T.ID WHEN MATCHED AND T.INVENTORY = S.SOLD THEN DELETE WHEN MATCHED AND T.INVENTORY < S.SOLD THEN SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT =:#M_TEXT || S.NAME WHEN MATCHED THEN UPDATE SET T.INVENTORY = T.INVENTORY - S.SOLD WHEN NOT MATCHED THEN INSERT VALUES(S.ID, S.NAME, -S.SOLD) END TRANSACTION END