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
This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
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)) .returningResult(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.
References to this page
- Updatable Primary Keys
- The WITH clause
- INSERT .. DEFAULT VALUES
- Defaults
- WITH CHECK OPTION
- Temporal tables
- Data change delta tables
- Readonly columns
- Tuples or row value expressions
- Conditional expressions
- CRUD with UpdatableRecords
- Simple CRUD
- CRUD SPI: RecordListener
- ExecuteListeners
- Logging with SQLExceptionLoggerListener
- Readonly columns
- SQL to DSL mapping rules
Feedback
Do you have any feedback about this page? We'd love to hear it!