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
WHEN MATCHED THEN UPDATE
Supported by ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
When there's a MATCH between SOURCE row and TARGET row, then the TARGET row can be updated with values from the SOURCE row, similar to what the UPDATE .. FROM clause would allow.
Dialect support
This example using jOOQ:
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)
Translates to the following dialect specific expressions:
Databricks, Postgres, Snowflake, Teradata, Vertica
MERGE INTO BOOK_TO_BOOK_STORE USING 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
DB2, Exasol, Firebird, H2, HSQLDB, Hana, Sybase
MERGE INTO BOOK_TO_BOOK_STORE USING 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 BOOK_TO_BOOK_STORE.STOCK = BOOK_TO_BOOK_STORE_STAGING.STOCK
Oracle
MERGE INTO BOOK_TO_BOOK_STORE USING 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 BOOK_TO_BOOK_STORE.STOCK = BOOK_TO_BOOK_STORE_STAGING.STOCK
Redshift
UPDATE BOOK_TO_BOOK_STORE
SET
STOCK = CASE
WHEN 1 = 1 THEN BOOK_TO_BOOK_STORE_STAGING.STOCK
ELSE BOOK_TO_BOOK_STORE.STOCK
END
FROM BOOK_TO_BOOK_STORE_STAGING
WHERE (
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
)
SQLServer
MERGE INTO BOOK_TO_BOOK_STORE USING 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 BOOK_TO_BOOK_STORE.STOCK = BOOK_TO_BOOK_STORE_STAGING.STOCK;
ASE, Access, Aurora MySQL, Aurora Postgres, BigQuery, ClickHouse, CockroachDB, DuckDB, Informix, MariaDB, MemSQL, MySQL, SQLDataWarehouse, SQLite, Spanner, Trino, YugabyteDB
/* UNSUPPORTED */
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!