Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16

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:

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 */
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!

The jOOQ Logo