The jOOQ User Manual SQL building Table expressions Data change delta tables

previous : next

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 (OLD, NEW, FINAL)
• 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 DEFAULT and 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 TRIGGERS are 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

Dialect support

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

Firebird

INSERT INTO BOOK (ID, TITLE)
VALUES (
  1, 
  'The Book'
)
RETURNING BOOK.ID

MariaDB

INSERT INTO BOOK (ID, TITLE)
VALUES (
  1, 
  'The Book'
)
RETURNING ID

ASE, Access, Aurora MySQL, BigQuery, ClickHouse, Derby, DuckDB, Exasol, HSQLDB, Hana, Informix, MemSQL, MySQL, Oracle, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Sybase, Teradata, Trino, Vertica, YugabyteDB

/* UNSUPPORTED */

The jOOQ User Manual SQL building Table expressions Data change delta tables

previous : next