Share jOOQ on Facebook
Share jOOQ on Twitter

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.equal(3));

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.equal(AUTHOR.ID))
      )
      .where(AUTHOR.ID.equal(3));

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.equal(3));

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.equal(AUTHOR.ID))
      )
      .where(AUTHOR.ID.equal(3));

The above row value expressions usages are completely typesafe.

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 UPDATE_COUNT
int count = create.update(BOOK)
                  .set(BOOK.TITLE, "Animal Farm")
                  .where(BOOK.ID.equal(5))
                  .returning(BOOK.UPDATE_COUNT)
                  .fetchOne().getValue(BOOK.UPDATE_COUNT);

The UPDATE .. RETURNING clause is currently not simulated for other databases. Future versions might execute an additional SELECT statement to fetch results.

The jOOQ Logo