Update

This chapter describes how to update records in the database.

set Fields and values to Update Configuration

In Kronos, we can use the KPojo.update().set method to set the fields and values that need to be updated, and then use the execute method to perform the update operation.

Use where() or by { ... } to choose target rows. An empty where() generates equality conditions from the current KPojo's queryable non-null fields.

update().execute() updates every row allowed by framework strategies. Enable DataGuard when your application should reject full-table writes.

For strategy fields added to generated updates, see 📚Logic Delete and 📚Optimistic Lock.

kotlin

val user: User = User(
    id = 1
)

user
    .update()
    .set { it.name = "Kronos ORM" }
    .where()
    .execute()

// Dynamically sets the assignment column based on the string:
//    .set { it["name"] = "Kronos ORM" }

Mysql

UPDATE `user`
SET `name` = :nameNew
WHERE `id` = :id

PostgreSQL

UPDATE "user"
SET "name" = :nameNew
WHERE "id" = :id

SQLite

UPDATE `user`
SET `name` = :nameNew
WHERE `id` = :id

SQLServer

UPDATE [user]
SET [name] = :nameNew
WHERE [id] = :id

Oracle

UPDATE "user"
SET "name" = :nameNew
WHERE "id" = :id

+= -= Compound assignment operator

Kronos supports semantic += and -= addition and subtraction assignment operations to update numeric columns in the database

kotlin

user
    .update()
    .set { it.age += 2 }
    .where()
    .execute()

// Dynamically sets the assignment column based on the string:
//    .set { it["age"] += 2 }

Mysql

UPDATE `user`
SET `age` = `age` + :age2PlusNew
WHERE `id` = :id

PostgreSQL

UPDATE "user"
SET "age" = "age" + :age2PlusNew
WHERE "id" = :id

SQLite

UPDATE `user`
SET `age` = `age` + :age2PlusNew
WHERE `id` = :id

SQLServer

UPDATE [user]
SET [age] = [age] + :age2PlusNew
WHERE [id] = :id

Oracle

UPDATE "user"
SET "age" = "age" + :age2PlusNew
WHERE "id" = :id

Kotlin expressions as assignment values

The right side of a set assignment can be a normal runtime Kotlin expression. Kronos binds the expression result as the assignment value.

kotlin

fun displayName(): String? = null

User(id = 1)
    .update()
    .set { it.name = displayName() ?: "Anonymous" }
    .where()
    .execute()

Mysql

UPDATE `user`
SET `name` = :nameNew
WHERE `id` = :id

Set a value from a scalar subquery

Use a scalar subquery in set when the new value should come from another query. The subquery selects one column and uses limit(1).

kotlin

Order()
    .update()
    .set {
        it.status = (Order()
            .select { order -> order.status }
            .where { order -> order.userId == 44 }
            .limit(1) as Int?)
    }
    .where { it.id == 3 }
    .execute()

Mysql

UPDATE `order`
SET `status` = (
    SELECT `status`
    FROM `order`
    WHERE `order`.`user_id` = :userId
    LIMIT 1
)
WHERE `order`.`id` = :id

by Update Condition Configuration

In Kronos, we can use the by method to set an update condition, at which point Kronos generates an update condition statement based on the fields set by the by method.

kotlin

val user: User = User(
    id = 1,
    name = "Kronos",
    age = 18
)

user
    .update()
    .set { it.name = "Kronos ORM" }
    .by { it.id }
    .execute()

Mysql

UPDATE `user`
SET `name` = :nameNew
WHERE `id` = :id

PostgreSQL

UPDATE "user"
SET "name" = :nameNew
WHERE "id" = :id

SQLite

UPDATE `user`
SET `name` = :nameNew
WHERE `id` = :id

SQLServer

UPDATE [user]
SET [name] = :nameNew
WHERE [id] = :id

Oracle

UPDATE "user"
SET "name" = :nameNew
WHERE "id" = :id

where Update Condition Configuration

where() generates query-by-example update conditions from the current object values. where { ... } generates an update 📚conditional expression from the lambda.

kotlin

val user: User = User(
    id = 1,
    name = "Kronos",
    age = 18
)

user
    .update()
    .set { it.name = "Kronos ORM" }
    .where { it.id == 1 }
    .execute()

Mysql

UPDATE `user`
SET `name` = :nameNew
WHERE `id` = :id

PostgreSQL

UPDATE "user"
SET "name" = :nameNew
WHERE "id" = :id

SQLite

UPDATE `user`
SET `name` = :nameNew
WHERE `id` = :id

SQLServer

UPDATE [user]
SET [name] = :nameNew
WHERE [id] = :id

Oracle

UPDATE "user"
SET "name" = :nameNew
WHERE "id" = :id

Filter updates with an IN subquery

Use field in query inside where to update rows selected by another query.

kotlin

User(name = "active")
    .update { [it.name] }
    .where {
        it.id in Order()
            .select { order -> order.userId }
            .where { order -> order.status == 1 }
    }
    .execute()

Mysql

UPDATE `user`
SET `name` = :nameNew
WHERE `user`.`id` IN (
    SELECT `user_id` AS `userId`
    FROM `order`
    WHERE `order`.`status` = :status
)

More scalar and predicate subquery forms are covered in 📚Subqueries.

Inside an explicit where block, .eq can expand the current KPojo object's field values into equality conditions, and you can combine those conditions with other expressions.

kotlin

val user: User = User(
    id = 1,
    name = "Kronos"
)

user.update { it.name }.where { it.eq && it.status > 1 }.execute()

Mysql

UPDATE `user`
SET `name` = :nameNew
WHERE `id` = :id
  and `name` = :name
  and `status` = :status
  and `status` > :statusMin

PostgreSQL

UPDATE "user"
SET "name" = :nameNew
WHERE "id" = :id
  and "name" = :name
  and "status" = :status
  and "status" > :statusMin

SQLite

UPDATE `user`
SET `name` = :nameNew
WHERE "id" = :id
  and "name" = :name
  and "status" = :status
  and "status" > :statusMin

SQLServer

UPDATE [user]
SET [name] = :nameNew
WHERE [id] = :id
  and [name] = :name
  and [status] = :status
  and [status] > :statusMin

Oracle

UPDATE "user"
SET "name" = :nameNew
WHERE "id" = :id
  and "name" = :name
  and "status" = :status
  and "status" > :statusMin

Kronos provides the minus operator - to exclude fields from the explicit .eq expansion.

kotlin

val user: User = User(
    id = 1,
    name = "Kronos"
)

user.update { it.name }.where { (it - it.name).eq && it.status > 1 }.execute()

Mysql

UPDATE `user`
SET `name` = :nameNew
WHERE `id` = :id
  and `status` > :statusMin

PostgreSQL

UPDATE "user"
SET "name" = :nameNew
WHERE "id" = :id
  and "status" > :statusMin

SQLite

UPDATE "user"
SET "name" = :nameNew
WHERE "id" = :id
  and "status" > :statusMin

SQLServer

UPDATE [user]
SET [name] = :nameNew
WHERE [id] = :id
  and [status] > :statusMin

Oracle

UPDATE "user"
SET "name" = :nameNew
WHERE "id" = :id
  and "status" > :statusMin

patch Adding Parameters to Custom Update Conditions

In Kronos, we can use the patch method to add parameters to custom update conditions.

kotlin

val user: User = User(
    id = 1,
    name = "Kronos",
    age = 18
)

user
    .update()
    .set { it.name = "Kronos ORM" }
    .where { "id = :id".asSql() }
    .patch("id" to 1)
    .execute()

Mysql

UPDATE `user`
SET `name` = :nameNew
WHERE id = :id

PostgreSQL

UPDATE "user"
SET "name" = :nameNew
WHERE id = :id

SQLite

UPDATE `user`
SET `name` = :nameNew
WHERE id = :id

SQLServer

UPDATE [user]
SET [name] = :nameNew
WHERE id = :id

Oracle

UPDATE "user"
SET "name" = :nameNew
WHERE id = :id

update Fields to Update Configuration

In Kronos, we set the fields that need to be updated in the update method, and fields not set in the update method will not be updated.

kotlin

val user: User = User(
    id = 1,
    name = "Kronos",
    age = 18
)

user
    .update { [it.name, it.age] }
    .where { it.id == 1 }
    .execute()

Mysql

UPDATE `user`
SET `name` = :nameNew,
    `age`  = :ageNew
WHERE `id` = :id

PostgreSQL

UPDATE "user"
SET "name" = :nameNew,
    "age"  = :ageNew
WHERE "id" = :id

SQLite

UPDATE `user`
SET `name` = :nameNew,
    `age`  = :ageNew
WHERE `id` = :id

SQLServer

UPDATE [user]
SET [name] = :nameNew, [age] = :ageNew
WHERE [id] = :id

Oracle

UPDATE "user"
SET "name" = :nameNew,
    "age"  = :ageNew
WHERE "id" = :id

update - Fields to Exclude Configuration

In Kronos, we can use the minus expression - to set the fields to be excluded.

kotlin

val user: User = User(
    id = 1,
    name = "Kronos",
    age = 18
)

user
    .update { it - it.id }
    .where { it.id == 1 }
    .execute()

Mysql

UPDATE `user`
SET `name` = :nameNew,
    `age`  = :ageNew
WHERE `id` = :id

PostgreSQL

UPDATE "user"
SET "name" = :nameNew,
    "age"  = :ageNew
WHERE "id" = :id

SQLite

UPDATE `user`
SET `name` = :nameNew,
    `age`  = :ageNew
WHERE `id` = :id

SQLServer

UPDATE [user]
SET [name] = :nameNew, [age] = :ageNew
WHERE [id] = :id

Oracle

UPDATE "user"
SET "name" = :nameNew,
    "age"  = :ageNew
WHERE "id" = :id

Affected Rows

In Kronos, we can use the execute method to perform an update operation and get the number of lines affected by the update operation.

val user: User = User(
    id = 1,
    name = "Kronos",
    age = 18
)

val (affectedRows) = user
    .update()
    .set { it.name = "Kronos ORM" }
    .execute()

Batch update records

Use 📚Batch Operations when the same SQL update should run with many parameter sets.

Specify the data source to be used

In Kronos, we can pass KronosDataSourceWrapper into the execute method to implement a customized database connection.

val customWrapper = CustomWrapper()

val user: User = User(
    id = 1,
    name = "Kronos",
    age = 18
)

user
    .update()
    .set { it.name = "Kronos ORM" }
    .execute(customWrapper)