O'Reilly logo

SQL in a Nutshell 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

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

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