SQL in a Nutshell, 3rd Edition

Name

MERGE Statement

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, the records in the target table are updated with the values of the records in the source table, based upon predefined conditions. If records that do not exist in the target table do exist in the source table, they are inserted into the target table. The MERGE statement was added in the SQL2003 release of the ANSI standard.

Platform	Command
MySQL	Not supported
Oracle	Supported
PostgreSQL	Supported
SQL Server	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 updatable view of object_name, or it may be a nested table subquery.

[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

Specifies the condition or conditions on which a match between the source and target table is evaluated. The syntax is essentially ...