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 .. RETURNING
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Various dialect support a RETURNING clause or something similar on their UPDATE statements, similar as the RETURNING clause in INSERT statements. This is useful to fetch trigger-generated values in one go. An example is given here:
-- Fetch a trigger-generated value UPDATE BOOK SET TITLE = 'Animal Farm' WHERE ID = 5 RETURNING TITLE
String title = create.update(BOOK)
.set(BOOK.TITLE, "Animal Farm")
.where(BOOK.ID.eq(5))
.returningResult(BOOK.TITLE)
.fetchOne().getValue(BOOK.TITLE);
Dialect support
This example using jOOQ:
update(BOOK).set(BOOK.TITLE, "New Title").returningResult(BOOK.ID)
Translates to the following dialect specific expressions:
Aurora Postgres, CockroachDB, Postgres, SQLite, YugabyteDB
UPDATE BOOK SET TITLE = 'New Title' RETURNING BOOK.ID
DB2, H2
SELECT ID
FROM FINAL TABLE (
UPDATE BOOK
SET
BOOK.TITLE = 'New Title'
) BOOK
Firebird
UPDATE BOOK SET BOOK.TITLE = 'New Title' RETURNING BOOK.ID
MariaDB
INSERT INTO BOOK ( ID, AUTHOR_ID, TITLE, PUBLISHED_IN, LANGUAGE_ID ) SELECT BOOK.ID, BOOK.AUTHOR_ID, BOOK.TITLE, BOOK.PUBLISHED_IN, BOOK.LANGUAGE_ID FROM BOOK ON DUPLICATE KEY UPDATE BOOK.TITLE = 'New Title' RETURNING ID
Oracle
DECLARE
o0 DBMS_SQL.NUMBER_TABLE;
c0 sys_refcursor;
BEGIN
UPDATE BOOK
SET
BOOK.TITLE = 'New Title'
RETURNING BOOK.ID
BULK COLLECT INTO o0;
? := SQL%ROWCOUNT;
OPEN c0 FOR SELECT * FROM TABLE(o0);
? := c0;
END;
SQLServer
UPDATE BOOK SET BOOK.TITLE = 'New Title' OUTPUT inserted.ID
ASE, Access, Aurora MySQL, BigQuery, ClickHouse, Databricks, Derby, DuckDB, Exasol, HSQLDB, Hana, Informix, MemSQL, MySQL, Redshift, SQLDataWarehouse, Snowflake, Sybase, Teradata, Trino, Vertica
/* UNSUPPORTED */
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website
References to this page
Feedback
Do you have any feedback about this page? We'd love to hear it!