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

Supported by ✅ 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

The following sections explain the MERGE statement in detail.