MERGE (SQL)

#### Search Results

Search

# MERGE (SQL)

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

This document covers the following topics:

- Function

- Restriction

- Syntax Description

- Examples

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.

## 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

- matching-condition

- modification-operation

- signal-operation

- values-single-row

- values-multiple-row

- update-operation

- assignment-clause

- insert-operation

### 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

### On this page

- Function
- Restriction
- Syntax Description
- Examples