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 |
[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 include,
along with the columns of the target table, in the result table of the
|
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 under which the
|
THEN
modification-operation |
THEN
modification-operation Clause:
Specifies the operation to run when 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 |
(VALUES |
) | |||
[AS ] correlation-name
(column-name),...
|
Syntax Element | Description |
---|---|
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. |
values-multiple-row |
Specifies multiple rows of source data. |
[AS]
correlation-name |
Specifies a correlation name for the source-table. |
column-name |
Specifies a column name to associate the input
data to the SET
assignment-clause clause for an
UPDATE operation or the
VALUES
clause for an INSERT operation.
|
expression | ||||
NULL |
||||
expression,... | ||||
NULL |
Syntax Element | Description |
---|---|
expression |
Specifies a scalar expression as described in scalar expressions. |
NULL |
Specifies the null value. |
expression | ||||
host-variable-array | ||||
NULL |
||||
expression,... | ||||
host-variable-array | ||||
NULL |
||||
FOR |
host-variable | ROWS |
||
integer-constant |
Syntax Element | Description |
---|---|
expression |
Specifies a scalar expression as described in scalar expressions. |
host-variable-array |
Specifies a host variable array or an index
range of an array. If
host-variable-array are specified, the
compiler option DB2ARRY has to be set to ON . An optional
indicator array can be specified for each host-variable-array by the
keyword INDICATOR , that is, host-variable-array INDICATOR [:]indicator-arry .
|
NULL |
Specifies the null value. |
FOR
... ROWS |
Specifies the number of rows to merge.
host-variable or
integer-constant is assigned to a value
k .
k must be in the range of
0 to 32767 and must be lower than or equal to the
minimum size of all specified host-variable-arrays.
|
MATCHED |
||
NOT
MATCHED |
UPDATE
SET assignment-clause |
||
insert-operation |
Syntax Element | Description |
---|---|
UPDATE SET |
Specifies the UPDATE operation to
run when the matching-condition evaluates to true.
See |
insert-operation |
Specifies the
insert-operation to run for the rows
where the matching condition evaluates to false.
|
column-name = | expression | ,... | ||||
DEFAULT |
||||||
NULL |
||||||
(column-name,...) = ( | expression,... | ,...) | ||||
DEFAULT |
||||||
NULL |
INSERT [(column-name,...)]
VALUES (
|
expression | ,...) | ||
DEFAULT |
||||
NULL |
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 marked with red color.
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