Update Statement

Basic Usage

The usage of the update statement is:

Java
Kotlin

AuthorTable author = Tables.AUTHOR_TABLE;

int affectedRowCount = sqlClient
    .createUpdate(author)
    .set(
        author.firstName(),
        author.firstName().concat("*")  
    )
    .where(author.firstName().eq("Dan"))
    .execute();
System.out.println("Affected row count: " + affectedRowCount);

val affectedRowCount = sqlClient
    .createUpdate(Author::class) {
        set(
            table.firstName,
            concat(table.firstName, value("*"))
        )
        where(table.firstName eq "Dan")
    }
    .execute()
println("Affected row count: $affectedRowCount") 

The finally generated SQL is:

update AUTHOR tb_1_  
set FIRST_NAME = concat(tb_1_.FIRST_NAME, ?)
where tb_1_.FIRST_NAME = ?

Returning Updated Rows

If the updated values are needed immediately, the update can return selected columns from every affected row in the same database statement.

Java
Kotlin

AuthorTable author = Tables.AUTHOR_TABLE;

List<Tuple2<Long, String>> rows = sqlClient
    .createUpdate(author)
    .set(
        author.firstName(),
        author.firstName().concat("*")
    )
    .where(author.firstName().eq("Dan"))
    .returning(author.id(), author.firstName())
    .execute();

val rows: List<Tuple2<Long, String>> = sqlClient
    .createUpdateReturning(Author::class) {
        set(
            table.firstName,
            concat(table.firstName, value("*"))
        )
        where(table.firstName eq "Dan")
        returning(table.id, table.firstName)
    }
    .execute()

Java adds returning(...) to the MutableUpdate created by createUpdate. Kotlin uses createUpdateReturning because its lambda must return the executable created by returning(...); executeUpdateReturning is the corresponding shortcut that executes it immediately.

A single selection produces a list of that value. Multiple selections produce a tuple, and Java can also use a generated typed tuple mapper. Like queries, the returned rows can be consumed as a list with execute() or as a closeable stream with stream():

Java
Kotlin

try (Stream<Tuple2<Long, String>> rows = sqlClient
        .createUpdate(author)
        .set(author.firstName(), "Daniel")
        .where(author.firstName().eq("Dan"))
        .returning(author.id(), author.firstName())
        .stream()) {
    rows.forEach(System.out::println);
}

sqlClient.createUpdateReturning(Author::class) {
    set(table.firstName, "Daniel")
    where(table.firstName eq "Dan")
    returning(table.id, table.firstName)
}.stream().use { rows ->
    rows.forEach(::println)
}

Update returning is currently supported by H2Dialect and PostgresDialect. Using it with a dialect that does not support update returning throws an exception.

Only physical column properties belonging to the updated table can be selected. Associations, formulas, arbitrary expressions, and columns from joined tables cannot be returned. For JOINED inheritance, assignments and returned columns must belong to the same physical table.

Unlike save result fetching, this is an explicit DML API: it does not build a residual fetcher or execute a fallback select. If a complete entity graph is required after a save, use saveCommand(...).execute(fetcher) instead.

Using JOIN

By default, update statements do not support joins, which will cause exceptions:

Java
Kotlin

AuthorTableEx author = TableExes.AUTHOR_TABLE_EX;

int affectedRowCount = sqlClient
    .createUpdate(author)
    .set(
        author.firstName(),
        author.firstName().concat("*")
    )
    .where(
        author
            .books()
            .name()
            .eq("Learning GraphQL") 
    )
    .execute();
System.out.println("Affected row count: " + affectedRowCount);

val affectedRowCount = sqlClient
    .createUpdate(Author::class) {
        set(
            table.firstName, 
            concat(table.firstName, value("*"))
        )
        where(
            table
                .books
                .name
                eq "Learning GraphQL"
        )
    }
    .execute()
println("Affected row count: $affectedRowCount")

The exception message is:

Table joins for update statement is forbidden by the current dialect, but there is a join `'Author.books'`.

When using MySQL or Postgres, update statements can use JOIN clauses.

MySQL

First, MySQLDialect needs to be specified when creating SqlClient:

Spring Boot configuration:

Declare dialect in application.yml or application.properties, like this
```
jimmer:
    dialect: org.babyfish.jimmer.sql.dialect.MySqlDialect
```

Non-Spring Boot configuration:

Java
Kotlin

JSqlClient sqlClient = JSqlClient
    .newBuilder()
    .setDialect(
        new org.babyfish.jimmer.sql.dialect.MySqlDialect()
    )
    ...
    .build();

val sqlClient = newKSqlClient {
    setDialect(org.babyfish.jimmer.sql.dialect.MySqlDialect())
}

Then JOIN can be used in update:

Java
Kotlin

AuthorTableEx author = TableExes.AUTHOR_TABLE_EX;

int affectedRowCount = sqlClient
    .createUpdate(author) 
    .set(
        author.firstName(),
        author.firstName().concat("*")
    )
    .set(
        author.books().name(),
        author.books().name().concat("*")  
    )
    .set(
        author.books().store().name(),
        author.books().store().name().concat("*")
    )
    .where(
        author.books().store().name().eq("MANNING")
    )
    .execute();
System.out.println("Affected row count: " + affectedRowCount);

val affectedRowCount = sqlClient
    .createUpdate(Author::class) {
        set(
            table.firstName,
            concat(table.firstName, value("*")) 
        )
        set(
            table.books.name,
            concat(table.books.name, value("*"))
        )
        set(
            table.books.store.name, 
            concat(table.books.store.name, value("*"))
        )
        where(
            table.books.store.name eq "MANNING"  
        )
    }
    .execute()
println("Affected row count: $affectedRowCount")

The finally generated SQL for MySQL is:

update
    AUTHOR tb_1_
    inner join BOOK_AUTHOR_MAPPING as tb_2_
        on tb_1_.ID = tb_2_.AUTHOR_ID
    inner join BOOK as tb_3_
        on tb_2_.BOOK_ID = tb_3_.ID
    inner join BOOK_STORE as tb_4_
        on tb_3_.STORE_ID = tb_4_.ID
set
    tb_1_.FIRST_NAME = concat(tb_1_.FIRST_NAME, ?),
    tb_3_.NAME = concat(tb_3_.NAME, ?),
    tb_4_.NAME = concat(tb_4_.NAME, ?)
where
    tb_4_.NAME = ? 

Postgres

First, PostgresDialect needs to be specified when creating SqlClient:

Spring Boot configuration:

Declare dialect in application.yml or application.properties, like this
```
jimmer:
    dialect: org.babyfish.jimmer.sql.dialect.PostgresDialect
```

Non-Spring Boot configuration:

Java
Kotlin

JSqlClient sqlClient = JSqlClient
    .newBuilder()
    .setDialect(
        new org.babyfish.jimmer.sql.dialect.PostgresDialect()
    )
    ...
    .build();

val sqlClient = newKSqlClient {
    setDialect(org.babyfish.jimmer.sql.dialect.PostgresDialect())
}

Then JOIN can be used in update:

Java
Kotlin

AuthorTableEx author = TableExes.AUTHOR_TABLE_EX;

int affectedRowCount = sqlClient
    .createUpdate(author)
    .set(
        author.firstName(),
        author.firstName().concat("*")
    ) 
    .where(
        author.books().store().name().eq("MANNING")
    )
    .execute();
System.out.println("Affected row count: " + affectedRowCount);

val affectedRowCount = sqlClient
    .createUpdate(Author::class) {
        set(
            table.firstName,
            concat(table.firstName, value("*"))
        )
        where(
            table.books.store.name eq "MANNING" 
        )
    }
    .execute()
println("Affected row count: $affectedRowCount")

caution

Unlike MySQL, using JOIN in update statements in Postgres has the following restrictions:

JOIN can only be used in the where clause, not in the set clause. That is, Postgres still only allows modifying fields of the current table, supporting joins to other tables only for condition filtering.
The join path can have multiple levels, such as author.books().store(), where books() is level 1 and store() is level 2. The join type of the first level must be inner join.

The finally generated SQL for Postgres is:

update
    AUTHOR tb_1_
set
    FIRST_NAME = concat(tb_1_.FIRST_NAME, ?)
from BOOK_AUTHOR_MAPPING as tb_2_ ❶ 
inner join BOOK as tb_3_ ❷
    on tb_2_.BOOK_ID = tb_3_.ID
inner join BOOK_STORE as tb_4_ ❸
    on tb_3_.STORE_ID = tb_4_.ID
where
    tb_1_.ID = tb_2_.AUTHOR_ID ❹ 
and
    tb_4_.NAME = ?

info

The join path author.books().store() has two levels, books() is level 1 and store() is level 2.

Level 1 books() involves two tables:
- BOOK_AUTHOR_MAPPING table at ❶, but the join condition is missing here, supplemented at ❹.
- BOOK table at ❷.
Level 2 store() involves one table:
- BOOK_STORE table at ❸.

As you can see, in the update statement of Postgres, table joins directly related to the main table cannot use the join + on syntax, they must be equivalently transformed into from + where.

This is why Jimmer requires the join type of level 1 to be inner join for update statements in Postgres dialect.

Basic Usage​

Returning Updated Rows​

Using JOIN​

MySQL​

Postgres​

Basic Usage

Returning Updated Rows

Using JOIN

MySQL

Postgres