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
This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
UPDATE .. FROM
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Some databases, including for example PostgreSQL and SQL Server, support joining additional tables to an UPDATE
statement using a vendor-specific FROM
clause. This is supported as well by jOOQ:
UPDATE BOOK_ARCHIVE SET BOOK_ARCHIVE.TITLE = BOOK.TITLE FROM BOOK WHERE BOOK_ARCHIVE.ID = BOOK.ID
create.update(BOOK_ARCHIVE) .set(BOOK_ARCHIVE.TITLE, BOOK.TITLE) .from(BOOK) .where(BOOK_ARCHIVE.ID.eq(BOOK.ID)) .execute();
In many cases, such a joined update statement can be emulated using a correlated subquery, or using updatable views. For example, most databases allow for using scalar subselects in UPDATE
statements in one way or another. jOOQ models this through a set(Field<T>, Select<? extends Record1<T>>)
method in the UPDATE
DSL API, for convenience (see the section about scalar subqueries for more details):
UPDATE AUTHOR SET FIRST_NAME = ( SELECT FIRST_NAME FROM PERSON WHERE PERSON.ID = AUTHOR.ID ), WHERE ID = 3;
create.update(AUTHOR) .set(AUTHOR.FIRST_NAME, select(PERSON.FIRST_NAME) .from(PERSON) .where(PERSON.ID.eq(AUTHOR.ID)) ) .where(AUTHOR.ID.eq(3)) .execute();
Dialect support
This example using jOOQ:
update(BOOK_TO_BOOK_STORE).set(BOOK_TO_BOOK_STORE.STOCK, 0).from(BOOK).where(BOOK_TO_BOOK_STORE.BOOK_ID.eq(BOOK.ID)).and(BOOK.AUTHOR_ID.eq(1))
Translates to the following dialect specific expressions:
ASE, BigQuery, Oracle, SQLServer, Sybase
UPDATE BOOK_TO_BOOK_STORE SET BOOK_TO_BOOK_STORE.STOCK = 0 FROM BOOK WHERE ( BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID AND BOOK.AUTHOR_ID = 1 )
Aurora MySQL, MariaDB, MemSQL, MySQL, Trino
UPDATE BOOK_TO_BOOK_STORE CROSS JOIN BOOK SET BOOK_TO_BOOK_STORE.STOCK = 0 WHERE ( BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID AND BOOK.AUTHOR_ID = 1 )
Aurora Postgres, CockroachDB, DuckDB, Postgres, SQLite, Snowflake, YugabyteDB
UPDATE BOOK_TO_BOOK_STORE SET STOCK = 0 FROM BOOK WHERE ( BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID AND BOOK.AUTHOR_ID = 1 )
ClickHouse
UPDATE BOOK_TO_BOOK_STORE SET STOCK = 0 WHERE (NAME, BOOK_ID) IN ( SELECT BOOK_TO_BOOK_STORE.NAME, BOOK_TO_BOOK_STORE.BOOK_ID FROM BOOK_TO_BOOK_STORE, BOOK WHERE ( BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID AND BOOK.AUTHOR_ID = 1 ) )
Databricks
UPDATE BOOK_TO_BOOK_STORE SET STOCK = 0 WHERE EXISTS ( SELECT alias_1.v0, alias_1.v1 FROM ( SELECT BOOK_TO_BOOK_STORE.NAME v0, BOOK_TO_BOOK_STORE.BOOK_ID v1 FROM BOOK_TO_BOOK_STORE, BOOK WHERE ( BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID AND BOOK.AUTHOR_ID = 1 ) ) alias_1 WHERE ( coalesce(BOOK_TO_BOOK_STORE.NAME), coalesce(BOOK_TO_BOOK_STORE.BOOK_ID) ) = ( coalesce(alias_1.v0), coalesce(alias_1.v1) ) )
DB2, Derby, Exasol, Firebird, H2, HSQLDB
MERGE INTO BOOK_TO_BOOK_STORE USING BOOK ON ( BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID AND BOOK.AUTHOR_ID = 1 ) WHEN MATCHED THEN UPDATE SET BOOK_TO_BOOK_STORE.STOCK = 0
Hana
UPDATE BOOK_TO_BOOK_STORE FROM BOOK_TO_BOOK_STORE, BOOK SET BOOK_TO_BOOK_STORE.STOCK = 0 WHERE ( BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID AND BOOK.AUTHOR_ID = 1 )
Redshift
UPDATE BOOK_TO_BOOK_STORE SET STOCK = 0 WHERE (BOOK_TO_BOOK_STORE.NAME, BOOK_TO_BOOK_STORE.BOOK_ID) IN ( SELECT BOOK_TO_BOOK_STORE.NAME, BOOK_TO_BOOK_STORE.BOOK_ID FROM BOOK_TO_BOOK_STORE, BOOK WHERE ( BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID AND BOOK.AUTHOR_ID = 1 ) )
SQLDataWarehouse
UPDATE BOOK_TO_BOOK_STORE SET BOOK_TO_BOOK_STORE.STOCK = 0 WHERE EXISTS ( SELECT alias_1.v0, alias_1.v1 FROM ( SELECT BOOK_TO_BOOK_STORE.NAME v0, BOOK_TO_BOOK_STORE.BOOK_ID v1 FROM BOOK_TO_BOOK_STORE, BOOK WHERE ( BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID AND BOOK.AUTHOR_ID = 1 ) ) alias_1 WHERE ( BOOK_TO_BOOK_STORE.NAME = alias_1.v0 AND BOOK_TO_BOOK_STORE.BOOK_ID = alias_1.v1 ) )
Teradata, Vertica
MERGE INTO BOOK_TO_BOOK_STORE USING BOOK ON ( BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID AND BOOK.AUTHOR_ID = 1 ) WHEN MATCHED THEN UPDATE SET STOCK = 0
Access, Informix
/* 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!