O'Reilly logo

SQL in a Nutshell, 3rd Edition by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required