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, Redshift, 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 )
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!