Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
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:
-- ACCESS, ASE, 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, SNOWFLAKE, SQLITE, 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.19, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!