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
The UPDATE statement
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The UPDATE statement is used to modify one or several pre-existing records in a database table. UPDATE statements are only possible on single tables. Support for multi-table updates will be implemented in the near future. An example update query is given here:
UPDATE AUTHOR
SET FIRST_NAME = 'Hermann',
LAST_NAME = 'Hesse'
WHERE ID = 3;
create.update(AUTHOR)
.set(AUTHOR.FIRST_NAME, "Hermann")
.set(AUTHOR.LAST_NAME, "Hesse")
.where(AUTHOR.ID.eq(3))
.execute();
Most databases allow for using scalar subselects in UPDATE statements in one way or another. jOOQ models this through a set(Field<T>, Select<? extends Record1<T>>) method in the UPDATE DSL API:
UPDATE AUTHOR
SET FIRST_NAME = (
SELECT FIRST_NAME
FROM PERSON
WHERE PERSON.ID = AUTHOR.ID
),
WHERE ID = 3;
create.update(AUTHOR)
.set(AUTHOR.FIRST_NAME,
select(PERSON.FIRST_NAME)
.from(PERSON)
.where(PERSON.ID.eq(AUTHOR.ID))
)
.where(AUTHOR.ID.eq(3))
.execute();
Using row value expressions in an UPDATE statement
jOOQ supports formal row value expressions in various contexts, among which the UPDATE statement. Only one row value expression can be updated at a time. Here's an example:
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 subselects:
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.
UPDATE .. FROM
Some databases, including PostgreSQL and SQL Server, support joining additional tables to an UPDATE statement using a vendor-specific FROM clause. This is supported as well by jOOQ:
UPDATE BOOK_ARCHIVE SET BOOK_ARCHIVE.TITLE = BOOK.TITLE FROM BOOK WHERE BOOK_ARCHIVE.ID = BOOK.ID
create.update(BOOK_ARCHIVE)
.set(BOOK_ARCHIVE.TITLE, BOOK.TITLE)
.from(BOOK)
.where(BOOK_ARCHIVE.ID.eq(BOOK.ID))
.execute();
In many cases, such a joined update statement can be emulated using a correlated subquery, or using updatable views.
UPDATE .. RETURNING
The Firebird and Postgres databases support a RETURNING clause on their UPDATE statements, similar as the RETURNING clause in INSERT statements. This is useful to fetch trigger-generated values in one go. An example is given here:
-- Fetch a trigger-generated value UPDATE BOOK SET TITLE = 'Animal Farm' WHERE ID = 5 RETURNING TITLE
String title = create.update(BOOK)
.set(BOOK.TITLE, "Animal Farm")
.where(BOOK.ID.eq(5))
.returning(BOOK.TITLE)
.fetchOne().getValue(BOOK.TITLE);
The UPDATE .. RETURNING clause is emulated for DB2 using the SQL standard SELECT .. FROM FINAL TABLE(UPDATE ..) construct, and in Oracle, using the PL/SQL UPDATE .. RETURNING statement.
Feedback
Do you have any feedback about this page? We'd love to hear it!