Available in versions: Dev (3.21) | Latest (3.20) | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11

The MERGE statement

Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition

The MERGE statement is one of the most advanced standardised SQL constructs. While it can be used for UPSERT semantics (similar to INSERT .. ON DUPLICATE KEY UPDATE or INSERT .. ON CONFLICT), it is much more than that.

The point of the standard MERGE statement is to take a TARGET table, and merge (INSERT, UPDATE, or DELETE) data from a SOURCE table into it. Here is an example:

-- Check if there is already an author called 'Hitchcock'
-- If there is, rename him to John. If there isn't add him.
MERGE INTO AUTHOR
USING (SELECT 1 FROM DUAL)
ON (LAST_NAME = 'Hitchcock')
WHEN MATCHED THEN UPDATE SET FIRST_NAME = 'John'
WHEN NOT MATCHED THEN INSERT (LAST_NAME) VALUES ('Hitchcock');

create.mergeInto(AUTHOR)
      .using(create.selectOne())
      .on(AUTHOR.LAST_NAME.eq("Hitchcock"))
      .whenMatchedThenUpdate()
      .set(AUTHOR.FIRST_NAME, "John")
      .whenNotMatchedThenInsert(AUTHOR.LAST_NAME)
      .values("Hitchcock")
      .execute();

Think of the MERGE statement as a RIGHT JOIN between TARGET and SOURCE tables, where the ON clause defines whether there is a MATCH between TARGET and SOURCE rows:

If there's a MATCH, an UPDATE or DELETE operation on the TARGET row is possible
If there's no MATCH, then the SOURCE row can be used to perform an INSERT operation on the TARGET table
If there's no MATCH, some dialects also support the BY SOURCE clause to specify that a LEFT JOIN or FULL JOIN should be performed instead, in order to allow for performing an UPDATE or DELETE operation on existing rows in the TARGET table that have no matching row in the SOURCE table.

Multiple WHEN clauses

Various dialects support the standard SQL syntax for having multiple WHEN clauses, where an additional predicates are supplied with each clause.

MERGE INTO AUTHOR
USING (SELECT 1 FROM DUAL)
ON (LAST_NAME = 'Hitchcock')
WHEN MATCHED AND FIRST_NAME = 'Alfred'
  THEN UPDATE SET FIRST_NAME = 'John'
WHEN MATCHED AND FIRST_NAME = 'Diane'
  THEN UPDATE SET FIRST_NAME = 'Jane'
WHEN NOT MATCHED THEN INSERT (LAST_NAME)
VALUES
  ('Hitchcock');

create.mergeInto(AUTHOR)
      .using(create.selectOne())
      .on(AUTHOR.LAST_NAME.eq("Hitchcock"))
      .whenMatchedAnd(AUTHOR.FIRST_NAME.eq("Alfred"))
        .thenUpdate().set(AUTHOR.FIRST_NAME, "John")
      .whenMatchedAnd(AUTHOR.FIRST_NAME.eq("Diane"))
        .thenUpdate().set(AUTHOR.FIRST_NAME, "Jane")
      .whenNotMatchedThenInsert(AUTHOR.LAST_NAME)
      .values("Hitchcock")
      .execute();

If the above isn't supported in a dialect, it can be emulated by collapsing the various WHEN clauses into a single one that uses a CASE expression to cover all the conditions and results.

Dialect support

This example using jOOQ:

mergeInto(AUTHOR).using(selectOne()).on(AUTHOR.LAST_NAME.eq("Hitchcock")).whenMatchedThenUpdate().set(AUTHOR.FIRST_NAME, "John").whenNotMatchedThenInsert(AUTHOR.LAST_NAME).values("Hitchcock")

Translates to the following dialect specific expressions:

Databricks, Snowflake

MERGE INTO AUTHOR
USING (
  SELECT 1 one
)
ON AUTHOR.LAST_NAME = 'Hitchcock'
WHEN MATCHED THEN UPDATE SET
  FIRST_NAME = 'John'
WHEN NOT MATCHED THEN INSERT (LAST_NAME)
VALUES ('Hitchcock')

DB2

MERGE INTO AUTHOR
USING (
  SELECT 1 one
  FROM SYSIBM.DUAL
)
ON AUTHOR.LAST_NAME = 'Hitchcock'
WHEN MATCHED THEN UPDATE SET
  AUTHOR.FIRST_NAME = 'John'
WHEN NOT MATCHED THEN INSERT (LAST_NAME)
VALUES ('Hitchcock')

Derby

MERGE INTO AUTHOR
USING (
  SELECT 1 one
  FROM SYSIBM.SYSDUMMY1
)
ON AUTHOR.LAST_NAME = 'Hitchcock'
WHEN MATCHED THEN UPDATE SET
  AUTHOR.FIRST_NAME = 'John'
WHEN NOT MATCHED THEN INSERT (LAST_NAME)
VALUES ('Hitchcock')

Exasol, H2

MERGE INTO AUTHOR
USING (
  SELECT 1 one
)
ON AUTHOR.LAST_NAME = 'Hitchcock'
WHEN MATCHED THEN UPDATE SET
  AUTHOR.FIRST_NAME = 'John'
WHEN NOT MATCHED THEN INSERT (LAST_NAME)
VALUES ('Hitchcock')

Firebird

MERGE INTO AUTHOR
USING (
  SELECT 1 one
  FROM RDB$DATABASE
)
ON AUTHOR.LAST_NAME = 'Hitchcock'
WHEN MATCHED THEN UPDATE SET
  AUTHOR.FIRST_NAME = 'John'
WHEN NOT MATCHED THEN INSERT (LAST_NAME)
VALUES ('Hitchcock')

Hana

MERGE INTO AUTHOR
USING (
  SELECT 1 one
  FROM SYS.DUMMY
)
ON AUTHOR.LAST_NAME = 'Hitchcock'
WHEN MATCHED THEN UPDATE SET
  AUTHOR.FIRST_NAME = 'John'
WHEN NOT MATCHED THEN INSERT (LAST_NAME)
VALUES ('Hitchcock')

HSQLDB

MERGE INTO AUTHOR
USING (
  SELECT 1 one
  FROM (VALUES (1)) AS dual (dual)
)
ON AUTHOR.LAST_NAME = 'Hitchcock'
WHEN MATCHED THEN UPDATE SET
  AUTHOR.FIRST_NAME = 'John'
WHEN NOT MATCHED THEN INSERT (LAST_NAME)
VALUES ('Hitchcock')

Oracle

MERGE INTO AUTHOR
USING (
  SELECT 1 one
)
ON (AUTHOR.LAST_NAME = 'Hitchcock')
WHEN MATCHED THEN UPDATE SET
  AUTHOR.FIRST_NAME = 'John'
WHEN NOT MATCHED THEN INSERT (LAST_NAME)
VALUES ('Hitchcock')

Postgres, Vertica

MERGE INTO AUTHOR
USING (
  SELECT 1 one
) AS dummy_30260683("one")
ON AUTHOR.LAST_NAME = 'Hitchcock'
WHEN MATCHED THEN UPDATE SET
  FIRST_NAME = 'John'
WHEN NOT MATCHED THEN INSERT (LAST_NAME)
VALUES ('Hitchcock')

SQLServer

MERGE INTO AUTHOR
USING (
  SELECT 1 one
) AS dummy_30260683([one])
ON AUTHOR.LAST_NAME = 'Hitchcock'
WHEN MATCHED THEN UPDATE SET
  AUTHOR.FIRST_NAME = 'John'
WHEN NOT MATCHED THEN INSERT (LAST_NAME)
VALUES ('Hitchcock');

Sybase

MERGE INTO AUTHOR
USING (
  SELECT 1 one
  FROM SYS.DUMMY
) AS dummy_30260683([one])
ON AUTHOR.LAST_NAME = 'Hitchcock'
WHEN MATCHED THEN UPDATE SET
  AUTHOR.FIRST_NAME = 'John'
WHEN NOT MATCHED THEN INSERT (LAST_NAME)
VALUES ('Hitchcock')

Teradata

MERGE INTO AUTHOR
USING (
  SELECT 1 one
  FROM (
    SELECT 1 AS "dual"
  ) AS "dual"
)
ON AUTHOR.LAST_NAME = 'Hitchcock'
WHEN MATCHED THEN UPDATE SET
  FIRST_NAME = 'John'
WHEN NOT MATCHED THEN INSERT (LAST_NAME)
VALUES ('Hitchcock')

ASE, Access, Aurora MySQL, Aurora Postgres, BigQuery, ClickHouse, CockroachDB, DuckDB, Informix, MariaDB, MemSQL, MySQL, Redshift, SQLDataWarehouse, SQLite, Trino, YugabyteDB

/* UNSUPPORTED */

Generated with jOOQ 3.21. Translate your own SQL on our website

previous : next

References to this page

Feedback

Do you have any feedback about this page? We'd love to hear it!

Tell us a little bit about yourself and join our newsletter (examples)

The MERGE statement

Multiple WHEN clauses

Dialect support

Databricks, Snowflake

DB2

Derby

Exasol, H2

Firebird

Hana

HSQLDB

Oracle

Postgres, Vertica

SQLServer

Sybase

Teradata

ASE, Access, Aurora MySQL, Aurora Postgres, BigQuery, ClickHouse, CockroachDB, DuckDB, Informix, MariaDB, MemSQL, MySQL, Redshift, SQLDataWarehouse, SQLite, Trino, YugabyteDB

References to this page

Feedback

Community

Support

Legal

Documentation