MERGE - SQL

Function

Restriction

Syntax Description

Examples

`source-table`

`values-single-row`

`values-multiple-row`

`matching-condition`

`modification-operation`

`assignment-clause`

`insert-operation`

Example 1:

Example 2:


	Version 4.2.6 for Mainframes (Update)	SEARCH INDEX CONTENTS \| PDF PAGE PDF BOOKS \| HOME UP PREV NEXT

— Statements —

MERGE INTO table-name [[AS] correlation-name]

[include-columns] USING source-table

ON search-condition

WHEN matching-condition THEN modification-operation ...

[

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

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: `MERGE` 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 include, 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. 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 under which the `modification-operation` is run.
`THEN modification-operation`	THEN `modification-operation` Clause: Specifies the operation to run when the `matching-condition` is evaluates to true.
`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.

(`VALUES`		`values-single-row` `values-multiple-row`		)
[`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`

Syntax Element	Description
`MATCHED`	Specifies the operation to perform on the rows where the `ON search-condition` is true. Only `UPDATE` can be specified after the `THEN` keyword.
`NOT MATCHED`	Specifies the operation to perform on the rows where the `ON search-condition` is false. Only `INSERT` can be specified after the `THEN` keyword

	`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 `assignment-clause` below.
`insert-operation`	Specifies the `insert-operation` to run for the rows where the matching condition evaluates to false.

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` [(`column-name`,...)] `VALUES` (	`expression`	,...)
	`DEFAULT`
	`NULL`

Syntax Element	Description
`INSERT`	Specifies the `INSERT` operation to run for the rows where the matching condition evaluates to false.
`column-name`	Specifies the column for which a 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.

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

column-name =

expression

(column-name,...) = (

expression,...

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

SEARCH INDEX CONTENTS | PDF PAGE PDF BOOKS | HOME UP PREV NEXT