previous : next

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 features. 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 assuming that we have a BOOK_TO_BOOK_STORE_STAGING table that we use to load data into the "actual" BOOK_TO_BOOK_STORE data table when doing ETL: 

-- Merge staging data into the real table
MERGE INTO BOOK_TO_BOOK_STORE
USING (
  SELECT * FROM BOOK_TO_BOOK_STORE_STAGING
)
ON BOOK_TO_BOOK_STORE.BOOK_ID
   = BOOK_TO_BOOK_STORE_STAGING.BOOK_ID
AND BOOK_TO_BOOK_STORE.NAME
   = BOOK_TO_BOOK_STORE_STAGING.NAME
WHEN MATCHED THEN UPDATE
  SET STOCK = BOOK_TO_BOOK_STORE_STAGING.STOCK
WHEN NOT MATCHED THEN INSERT (
  BOOK_ID,
  NAME,
  STOCK
)
VALUES (
  BOOK_TO_BOOK_STORE_STAGING.BOOK_ID,
  BOOK_TO_BOOK_STORE_STAGING.NAME,
  BOOK_TO_BOOK_STORE_STAGING.STOCK
);
create.mergeInto(BOOK_TO_BOOK_STORE)
      .using(BOOK_TO_BOOK_STORE_STAGING)
      .on(BOOK_TO_BOOK_STORE.BOOK_ID
          .eq(BOOK_TO_BOOK_STORE_STAGING.BOOK_ID)
      .and(BOOK_TO_BOOK_STORE.NAME
          .eq(BOOK_TO_BOOK_STORE_STAGING.NAME)))
      .whenMatchedThenUpdate().set(
          BOOK_TO_BOOK_STORE.STOCK,
          BOOK_TO_BOOK_STORE_STAGING.STOCK
      )
      .whenNotMatchedThenInsert(
           BOOK_TO_BOOK_STORE.BOOK_ID,
           BOOK_TO_BOOK_STORE.NAME,
           BOOK_TO_BOOK_STORE.STOCK
      )
      .values(
           BOOK_TO_BOOK_STORE_STAGING.BOOK_ID,
           BOOK_TO_BOOK_STORE_STAGING.NAME,
           BOOK_TO_BOOK_STORE_STAGING.STOCK
       )
      .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..

The following sections explain the MERGE statement in detail.

Table of contents

3.5.7.1.
USING .. ON
3.5.7.2.
WHEN MATCHED THEN UPDATE
3.5.7.3.
WHEN MATCHED THEN DELETE
3.5.7.4.
WHEN MATCHED AND ..
3.5.7.5.
WHEN NOT MATCHED THEN INSERT
3.5.7.6.
WHEN NOT MATCHED AND .. (new)
3.5.7.7.
WHEN NOT MATCHED BY SOURCE (new)

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