Update Statement

Basic Usage

The usage of the update statement is:

Java

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);

Kotlin

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 = ?

Using JOIN

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

Java

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);

Kotlin

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

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

  Kotlin

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

Then JOIN can be used in update:

Java

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);

Kotlin

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

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

  Kotlin

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

Then JOIN can be used in update:

Java

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);

Kotlin

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:

1. 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.

2. 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.

1. Level 1 books() involves two tables:
   • BOOK_AUTHOR_MAPPING table at ❶, but the join condition is missing here, supplemented at ❹.
   • BOOK table at ❷.
2. 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.