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
Applies to ✅ 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, Derby, 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
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, Redshift, SQLDataWarehouse, SQLite, Trino, YugabyteDB
/* UNSUPPORTED */
Generated with jOOQ 3.21. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!