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!