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
USING .. ON
Supported by ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The USING .. ON clause specifies the MERGE statement SOURCE table and JOIN condition with the TARGET table, based on which the remaining clauses will be applied. By default, the SOURCE table is RIGHT JOIN-ed to the TARGET table, meaning that actions take place for every row in the SOURCE table.
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!