MERGE statement

The MERGE statement scans a table and either INSERTs, UPDATEs, or DELETEs rows depending on whether the rows satisfy a specified condition.

Syntax

MERGE INTO targetTable [ [ AS ] targetCorrelationName ]
USING sourceTable [ [ AS ] sourceCorrelationName ]
ON searchCondition mergeWhenClause [ mergeWhenClause ]*

Both targetTable and sourceTable are tableNames.

targetTable must identify a base table. targetTable may not be a transition table in a triggered statement, and it may not be a synonym.

sourceTable must identify a base table or a table function, and it may not be a synonym.

Both targetCorrelationName and sourceCorrelationName are correlationNames.

The unqualified source table name (or its correlation name) may not be the same as the unqualified target table name (or its correlation name).

The searchCondition is a Boolean expression. Columns referenced by the searchCondition must be in either targetTable or sourceTable. Functions mentioned in the searchCondition may not modify SQL data.

The row count for a successful MERGE statement is the total number of rows inserted, updated, and deleted by the statement.

Note: The MERGE statement is valid only after a database has been fully upgraded to Derby Release 10.11 or higher. (See "Upgrading a database" in the Derby Developer's Guide for more information.) This statement has no meaning in a database that is at Release 10.10 or lower.

mergeWhenClause

mergeWhenMatched | mergeWhenNotMatched

mergeWhenMatched

WHEN MATCHED [ AND matchRefinement ] THEN { mergeUpdate | DELETE }

The matchRefinement is a Boolean expression. Columns referenced by the matchRefinement must be in either targetTable or sourceTable. Functions mentioned in the matchRefinement may not modify SQL data.

mergeWhenNotMatched

WHEN NOT MATCHED [ AND matchRefinement ] THEN mergeInsert

Although permitted to do so by the SQL Standard, Derby does not currently support subqueries in WHEN [ NOT ] MATCHED clauses.

mergeUpdate

UPDATE SET column-Name = value [, column-Name = value ]*

Columns updated must be columns in targetTable.

Functions mentioned in the UPDATE values may not modify SQL data.

On the right side of SET operators for UPDATE actions, DEFAULT is the only value allowed for generated and identity columns.

No list of updated columns may mention the same column more than once.

The data types of updated values must be assignable to the corresponding columns according to the rules documented in Data type assignments and comparison, sorting, and ordering.

mergeInsert

INSERT [ ( Simple-column-Name [ , Simple-column-Name ]*  ) ] VALUES ( value [, value ]* )

Columns inserted must be columns in targetTable.

Functions mentioned in the INSERT values may not modify SQL data.

No list of inserted columns may mention identity columns, or may mention the same column more than once.

In a VALUES clause, DEFAULT is the only allowed value for generated columns.

The data types of inserted values must be assignable to the corresponding columns according to the rules documented in Data type assignments and comparison, sorting, and ordering.

Required privileges

The user who executes a MERGE statement must have the following privileges. See GRANT statement for information on privileges.

UPDATE privilege on every updated column of targetTable. A blanket UPDATE privilege on the entire targetTable would cover this.
INSERT privilege on targetTable if there are WHEN NOT MATCHED clauses.
DELETE privilege on targetTable if there are WHEN MATCHED ... THEN DELETE clauses.
EXECUTE privilege on all functions mentioned in the Boolean expressions and in the INSERT/UPDATE values.
USAGE privilege on all sequences and user-defined types mentioned in the Boolean expressions and in the INSERT/UPDATE values. See CREATE SEQUENCE statement and CREATE TYPE statement for more information.
SELECT privilege on all columns mentioned in the Boolean expressions and the value expressions of SET clauses.

MERGE statement behavior

The MERGE statement behaves as described in the following table.

Table 1. Merge statement behavior
Situation or Behavior	Description
Source table is empty	If the sourceTable is empty, a "no data" warning is raised with SQLState 02000.
An initial join is performed	Before any changes are made to targetTable, the sourceTable is joined to the targetTable by means of the ON clause. Call this join result J. Let N denote the rows in sourceTable missing from this join.
Clause order is important	The mergeWhenMatched and mergeWhenNotMatched clauses are applied in declaration order.
The first matched clause wins	For each row in J, Derby applies only the first mergeWhenMatched clause whose matchRefinement is satisfied.
The first not matched clause wins	For each row in N, Derby applies only the first mergeWhenNotMatched clause whose matchRefinement is satisfied.
Double dipping is not permitted	A cardinality violation is raised if a MERGE statement attempts to change (update or delete) the same row twice. This condition can occur if more than one source row joins to the same target row.

Examples

MERGE INTO hotIssues h
USING issues i
ON h.issueID = i.issueID
WHEN MATCHED AND i.lastUpdated = CURRENT_DATE 
    THEN UPDATE SET h.lastUpdated = i.lastUpdated
WHEN MATCHED AND i.lastUpdated < CURRENT_DATE THEN DELETE
WHEN NOT MATCHED AND i.lastUpdated = CURRENT_DATE 
    THEN INSERT VALUES ( i.issueID, i.lastUpdated );

MERGE INTO companies c
USING adhocInvoices a
ON a.companyName = c.companyName
WHEN NOT MATCHED THEN INSERT ( companyName ) VALUES ( a.companyName );

MERGE INTO warehouse.productList w
USING production.productList p
ON w.productID = p.productID
WHEN MATCHED and w.lastUpdated != p.lastUpdated
    THEN UPDATE SET lastUpdated = p.lastUpdated, 
                    description = p.description, 
                    price = p.price
WHEN NOT MATCHED
    THEN INSERT values ( p.productID, p.lastUpdated, p.description,
                         p.price );