Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10

UPDATE .. SET ROWS

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

The SET clause allows for setting ROW value expressions on updated records in a table.

UPDATE AUTHOR
   SET (FIRST_NAME, LAST_NAME) =
       ('Hermann',  'Hesse')
 WHERE ID = 3;
 
create.update(AUTHOR)
      .set(row(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME),
           row("Herman",          "Hesse"))
      .where(AUTHOR.ID.eq(3))
      .execute();

This can be particularly useful when using correlated subqueries in the SET clause, in case of which multiple columns can be updated with a single subquery, instead of only 1. See also UPDATE .. FROM for an alternative syntax for this scenario.

UPDATE AUTHOR
   SET (FIRST_NAME, LAST_NAME) = (
         SELECT PERSON.FIRST_NAME, PERSON.LAST_NAME
         FROM PERSON
         WHERE PERSON.ID = AUTHOR.ID
       )
 WHERE ID = 3;
 
create.update(AUTHOR)
      .set(row(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME),
           select(PERSON.FIRST_NAME, PERSON.LAST_NAME)
          .from(PERSON)
          .where(PERSON.ID.eq(AUTHOR.ID))
      )
      .where(AUTHOR.ID.eq(3))
      .execute();

The above row value expressions usages are completely typesafe.

Dialect support

This example using jOOQ:

update(BOOK).set(row(BOOK.TITLE, BOOK.LANGUAGE_ID), row("New Title", 1))

Translates to the following dialect specific expressions:

ASE, Access, Aurora MySQL, Derby, Exasol, Firebird, Informix, MariaDB, MemSQL, MySQL, SQLDataWarehouse, SQLServer, Sybase

UPDATE BOOK
SET
  BOOK.TITLE = 'New Title',
  BOOK.LANGUAGE_ID = 1

Aurora Postgres, CockroachDB, DB2, H2, HSQLDB, Trino

UPDATE BOOK
SET
  (TITLE, LANGUAGE_ID) = ('New Title', 1)

BigQuery

UPDATE BOOK
SET
  BOOK.TITLE = 'New Title',
  BOOK.LANGUAGE_ID = 1
WHERE TRUE

DuckDB, Redshift, SQLite, Snowflake, Teradata, Vertica

UPDATE BOOK
SET
  TITLE = 'New Title',
  LANGUAGE_ID = 1

Hana

UPDATE BOOK
FROM BOOK
SET
  (TITLE, LANGUAGE_ID) = ('New Title', 1)

Oracle

UPDATE BOOK
SET
  (TITLE, LANGUAGE_ID) = (
    SELECT 'New Title', 1
  )

Postgres, YugabyteDB

UPDATE BOOK
SET
  (TITLE, LANGUAGE_ID) = ROW ('New Title', 1)

(These are currently generated with jOOQ 3.20, see #10141), or translate your own on our website

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo