MERGE (SQL)

MERGE INTO table-name [[AS] correlation-name]
  [include-columns] USING source-table
  ON search-condition
{WHEN matching-condition THEN modification-operation} ../graphics/dot3.gif
[ELSE IGNORE]
  [NOT ATOMIC CONTINUE ON SQLEXCEPTION]
  [QUERYNO integer]

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


Function

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.

Restriction

This statement is available only with Natural for Db2.

Syntax Description

Syntax Element Description
MERGE INTO
MERGE INTO Clause:

MERGE INTO initiates an SQL MERGE statement, which is a combination of an SQL INSERT and an SQL Searched UPDATE statement.

table-name
Table Name:

Identifies the target of the INSERT or UPDATE operation of the MERGE statement.

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 MERGE statement if it is nested in the FROM clause in a SELECT statement. The included columns are appended to end of the column list identified by the target table.

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 source-table.

ON search-condition
ON search-condition Clause:

Specifies join conditions between the source-table and the target table. Each column name in the search condition must name a column of the target table or source-table.

WHEN matching-condition
WHEN matching-condition Clause:

Specifies the condition for which to perform the modification operation defined in the following THEN clause. See matching-condition.

THEN modification-operation
THEN modification-operation Clause:

Specifies the operation to perform on the matches of the condition defined in the preceding WHEN clause. See modification-operation.

ELSE IGNORE
ELSE IGNORE Clause:

Specifies that no action is taken on the source columns that do not match the condition specified in the WHEN clause.

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 EXPLAIN output and Db2 trace records.

source-table

table-reference
(VALUES

values-single-row
values-multiple-row

)
  [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.

matching-condition

[NOT] MATCHED [AND search-condition]
Syntax Element Description
[NOT] MATCHED Specifies the modification-operation to perform when an ON search-condition evaluates to true or not true (NOT can be specified optionally).
AND search-condition Specifies an (optional) additional condition to evaluate to true before the modification-operation performs.

modification-operation

update-operation)
DELETE
signal-operation
insert-operation
Syntax Element Description
update-operation Specifies that the matching target row is updated with the values assigned in the UPDATE SET assignment clause. An UPDATE operation is only allowed if the matching-condition evaluates to true.
DELETE Specifies that the matching target row is deleted. A DELETE operation is only allowed if the matching-condition evaluates to true.
signal-operation Specifies the SQL error to raise. A SIGNAL operation is only allowed if the matching-condition evaluates to true.
insert-operation Specifies the rows to insert into the target table. An INSERT operation is only allowed if the matching-condition evaluates to not true.

signal-operation

SIGNAL SQLSTATE [VALUE] sqlstate [SET MESSAGE_TEXT = scalar-expression]
Syntax Element Description
SIGNAL Specifies the SIGNAL operation to perform when the matching-condition evaluates to true. Db2 sets an SQLCODE -438 if an error is raised by the SIGNAL statement.
SQLSTATE [VALUE] sqlstate Specifies the SQLSTATE to be set by Db2. sqlstate is a 5-character alphanumeric constant or an alphanumeric variable. sqlstate values are assigned to SQLSTATE by Db2. See the appropriate Db2 documentation for recommended values.
SET MESSAGE_TEXT= scalar-expression This optional clause specifies an error or warning message which is placed into the SQLEERRMC field of the SQLCA or which can be retrieved with the GET DIAGNOSTICS statement.

values-single-row

 

expression
NULL

 

(

expression
NULL

,...)
Syntax Element Description
expression
Scalar Expression:

Specifies a scalar expression as described in Scalar Expressions.

NULL
NULL Value:

Specifies the null value.

values-multiple-row

 

expression
host-variable-array
NULL

 

FOR

host-variable
integer-constant

ROWS
(

expression
host-variable-array
NULL

,...)
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 host-variable-array is 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-array.

NULL
NULL Value:

Specifies the null value.

FOR ... ROWS
Number of Rows to Merge:

This clause 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.

update-operation

UPDATE SET assignment-clause
Syntax Element Description
UPDATE SET Specifies the UPDATE operation to be performed when the search-condition evaluates to true.

See assignment-clause below.

assignment-clause

column-name =

expression

 

,...
DEFAULT
NULL
(column-name,...) = (

expression

,...)
DEFAULT
NULL
Syntax Element Description
column-name Specifies the column for which an insert value is provided.
expression Specifies the new value for the column. An expression can contain references to columns of the source-table or target table. expression cannot contain references to included columns.
DEFAULT Specifies the default value for the column. The value that is assigned depends on how the column is defined.
NULL Specifies the null value as new value for the column.

insert-operation

INSERT [(column-name,...)] VALUES (

expression
DEFAULT)
NULL

,...)
Syntax Element Description
INSERT Specifies the INSERT operation to be performed when the search-condition evaluates to false.
column-name Specifies the column for which an insert value is provided.
VALUES Introduces one or more column values to insert.
expression Specifies the new value for the column.

An expression can contain references to columns of the source table. expression cannot contain references to columns of the target table.

DEFAULT Specifies the default value for the column.

The value that is assigned depends on how the column is defined.

NULL Specifies the null value as new value for the column.

Examples

Example 1:

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

Example 2:

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

Example 3:

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