SQL in a Nutshell, 2nd Edition

Name

MERGE Statement

Synopsis

The MERGE statement is sort of like a CASE statement for DML operations. It combines UPDATE and INSERT statements into a single atomic statement with either/or functionality.

Basically, MERGE examines the records of a source table and a target table. If the records exist in both the source and target tables, based upon predefined conditions, then records in the target table are updated with the values of the records in the source table. If records do not exist in the target table that do exist in the source table, then they are inserted into the target table. The MERGE statement was added in the SQL2003 release of the ANSI standard.

Platform	Command
DB2	Supported
MySQL	Not supported
Oracle	Supported
PostgreSQL	Not supported
SQL Server	Not supported

SQL2003 Syntax

MERGE INTO {object_name | subquery} [ [AS] alias ]
USING table_reference [ [AS] alias]
ON search_condition
WHEN MATCHED
   THEN UPDATE SET column = { expression | DEFAULT } [, ...]
 WHEN NOT MATCHED
   THEN INSERT [( column [, ...] )] VALUES ( expression [, ...] )

Keywords

MERGE INTO {object_name | subquery}

Declares the target object of the merge operation. The target object may be a table or updateable view of object_name, or the target object may be a nested table subquery rather than an explicitly declared table or updateable view.

[AS] alias

Provides an optional alias for the target table.

USING table_reference

Declares the source table, view, or subquery of the merge operation.

ON search_condition ...