previous : next

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

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

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:

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')

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')

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

↑ Back to top

Community

Support

Legal

Documentation

The jOOQ Logo