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.
Data change delta tables
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The SQL standard specifies how to turn a DML statement into a table expression that can be used in the FROM clause of a SELECT statement. Other dialects support a RETURNING or OUTPUT clause of some sort to produce the same behaviour, though less powerful.
A data change delta table has two parts:
- The result option (
- The data change statement, which includes DELETE, INSERT, MERGE, UPDATE
You can thus express a query like the following to return all the inserted data (including
TRIGGER generated values):
SELECT * FROM FINAL TABLE ( INSERT INTO BOOK (ID, TITLE) VALUES (1, 'The Book') )
create.select() .from(finalTable( insertInto(BOOK) .columns(BOOK.ID, BOOK.TITLE) .values(1, "The Book") )) .fetch();
Following the restrictions implemented by your dialect, the results of such tables can be further processed, projected, etc.
The semantics of the result options are:
OLD: Access the row data as it was prior to being modified by the data change statement. This does not work for INSERT
NEW: Access the row data as it is after being modified by the data change statement, but before any
AFTER TRIGGERSare fired. This does not work for DELETE
FINAL: Access the row data as it is after being modified by the data change statement, and all triggers. The data is in its "final" form. This does not work for DELETE
This example using jOOQ:
select(BOOK.ID).from(finalTable(insertInto(BOOK).columns(BOOK.ID, BOOK.TITLE).values(1, "The Book")))
Translates to the following dialect specific expressions:
-- AURORA_POSTGRES, COCKROACHDB, POSTGRES WITH BOOK AS ( INSERT INTO BOOK (ID, TITLE) VALUES ( 1, 'The Book' ) RETURNING BOOK.ID, BOOK.AUTHOR_ID, BOOK.TITLE, BOOK.PUBLISHED_IN, BOOK.LANGUAGE_ID ) SELECT BOOK.ID FROM BOOK BOOK -- DB2, H2 SELECT BOOK.ID FROM FINAL TABLE ( INSERT INTO BOOK (ID, TITLE) VALUES ( 1, 'The Book' ) ) BOOK -- ACCESS, ASE, AURORA_MYSQL, BIGQUERY, DERBY, DUCKDB, EXASOL, FIREBIRD, HANA, HSQLDB, INFORMIX, MARIADB, MEMSQL, MYSQL, -- ORACLE, REDSHIFT, SNOWFLAKE, SQLDATAWAREHOUSE, SQLITE, SQLSERVER, SYBASE, TERADATA, TRINO, VERTICA, YUGABYTEDB /* UNSUPPORTED */