Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
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 ) )
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.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!