Delete

This chapter describes how to delete records from the database.

Delete records with explicit conditions

In Kronos, we can use the KPojo.delete().execute() function to delete records in the database.

An empty where() reads queryable non-null fields from the current KPojo object and generates delete conditions. Use by { ... } to choose object fields for matching, and use where { ... } to write custom conditions.

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

For delete statements that write a marker column, see 📚Logic Delete.

kotlin

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

user.delete().where().execute()

Mysql

DELETE
FROM `user`
WHERE `id` = :id
  and `name` = :name
  and `age` = :age

PostgreSQL

DELETE
FROM "user"
WHERE "id" = :id
  and "name" = :name
  and "age" = :age

SQLite

DELETE
FROM `user`
WHERE `id` = :id
  and `name` = :name
  and `age` = :age

SQLServer

DELETE
FROM [user]
WHERE [id] = :id and [name] = :name and [age] = :age

Oracle

DELETE
FROM "user"
WHERE "id" = :id
  and "name" = :name
  and "age" = :age

by Delete Condition Configuration

In Kronos, we can use the by function to set the deletion condition, at which point Kronos will generate the deletion condition statement based on the field set by the by method.

kotlin

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

user.delete().by { it.id }.execute()

Mysql

DELETE
FROM `user`
WHERE `id` = :id

PostgreSQL

DELETE
FROM "user"
WHERE "id" = :id

SQLite

DELETE
FROM "user"
WHERE "id" = :id

SQLServer

DELETE
FROM [user]
WHERE [id] = :id

Oracle

DELETE
FROM "user"
WHERE "id" = :id

where Delete Condition Configuration

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

kotlin

val user: User = User(
    id = 1,
)

user.delete().where { it.id.eq && it.name like "Kronos%" and it.age > 18 }.execute()

Mysql

DELETE
FROM `user`
WHERE `id` = :id
  and `name` like :name
  and `age` > :ageMin

PostgreSQL

DELETE
FROM "user"
WHERE "id" = :id
  and "name" like :name
  and "age" > :ageMin

SQLite

DELETE
FROM "user"
WHERE "id" = :id
  and "name" like :name
  and "age" > :ageMin

SQLServer

DELETE
FROM [user]
WHERE [id] = :id and [name] like :name and [age] > :ageMin

Oracle

DELETE
FROM "user"
WHERE "id" = :id
  and "name" like :name
  and "age" > :ageMin

Filter deletes with an IN subquery

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

in

User()
    .delete()
    .where {
        it.id in Order()
            .select { order -> order.userId }
            .where { order -> order.status == 3 }
    }
    .execute()

in sql

DELETE FROM `user`
WHERE `user`.`id` IN (
    SELECT `user_id` AS `userId`
    FROM `order`
    WHERE `order`.`status` = :status
)

Filter deletes with EXISTS

Use exists(query) when a related row should decide whether the target row is deleted. The subquery can reference the outer where receiver.

exists

User()
    .delete()
    .where { user ->
        exists(
            Order()
                .select()
                .where { order -> order.userId == user.id && order.status == 0 }
        )
    }
    .execute()

exists sql

DELETE FROM `user`
WHERE EXISTS (
    SELECT `id`, `user_id` AS `userId`, `status`
    FROM `order`
    WHERE `order`.`user_id` = `user`.`id`
      AND `order`.`status` = :status
)

More 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.delete().where { it.eq && it.status > 1 }.execute()

Mysql

DELETE
FROM `user`
WHERE `id` = :id
  and `name` = :name
  and `status` = :status
  and `status` > :statusMin

PostgreSQL

DELETE
FROM "user"
WHERE "id" = :id
  and "name" = :name
  and "status" = :status
  and "status" > :statusMin

SQLite

DELETE
FROM "user"
WHERE "id" = :id
  and "name" = :name
  and "status" = :status
  and "status" > :statusMin

SQLServer

DELETE
FROM [user]
WHERE [id] = :id
  and [name] = :name
  and [status] = :status
  and [status] > :statusMin

Oracle

DELETE
FROM "user"
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.delete().where { (it - it.name).eq && it.status > 1 }.execute()

Mysql

DELETE
FROM `user`
WHERE `id` = :id
  and `status` > :statusMin

PostgreSQL

DELETE
FROM "user"
WHERE "id" = :id
  and "status" > :statusMin

SQLite

DELETE
FROM "user"
WHERE "id" = :id
  and "status" > :statusMin

SQLServer

DELETE
FROM [user]
WHERE [id] = :id
  and [status] > :statusMin

Oracle

DELETE
FROM "user"
WHERE "id" = :id
  and "status" > :statusMin

patch Add parameters for custom deletion conditions

In Kronos, we can use the patch method to add parameters to a custom delete condition.

kotlin

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

user.delete().where { it.eq && "status = :status".asSql() }.patch("status" to 1).execute()

Mysql

DELETE
FROM `user`
WHERE `id` = :id
  and status = :status

PostgreSQL

DELETE
FROM "user"
WHERE "id" = :id
  and status = :status

SQLite

DELETE
FROM `user`
WHERE `id` = :id
  and status = :status

SQLServer

DELETE
FROM [user]
WHERE [id] = :id
  and status = :status

Oracle

DELETE
FROM "user"
WHERE "id" = :id
  and status = :status

logic Logical deletion

In Kronos, we can set up logical deletion using the logic method, at which point Kronos generates the SQL statement for logical deletion.

Please refer to Enabling Logical Deletion and Field Setting Settings for logical deletion: 📚Global Config - Logical Deletion Strategy, 📚Annotation Config - @LogicDeleteTable Logical Deletion, 📚Annotation Config - @LogicDeleteLogical Deletion Column.

kotlin

val user: User = User(
    id = 1,
)

user.delete().by { it.id }.logic().execute()

Mysql

UPDATE `user`
SET `deleted` = 1
WHERE `id` = :id

PostgreSQL

UPDATE "user"
SET "deleted" = 1
WHERE "id" = :id

SQLite

UPDATE `user`
SET `deleted` = 1
WHERE `id` = :id

SQLServer

UPDATE [user]
SET [deleted] = 1
WHERE [id] = :id

Oracle

UPDATE "user"
SET "deleted" = 1
WHERE "id" = :id

When logical deletion uses a subquery condition, Kronos keeps the subquery predicate and adds the logical-delete predicate.

kotlin

User()
    .delete()
    .logic()
    .where {
        it.id in Order()
            .select { order -> order.userId }
            .where { order -> order.status == 5 }
    }
    .execute()

Mysql

UPDATE `user`
SET `deleted` = :deletedNew
WHERE `user`.`id` IN (
    SELECT `user_id` AS `userId`
    FROM `order`
    WHERE `order`.`status` = :status
)
AND `deleted` = 0

Affected rows

The KronosOperationResult object returned by the execute method contains the number of affected rows.

kotlin

val user: User = User(
    id = 1,
)

val (affectedRows) = user.delete().by { it.id }.execute()

Batch delete records

Use 📚Batch Operations when the same SQL delete 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.

kotlin

val customWrapper = CustomWrapper()

val user: User = User(
    id = 1,
)

user.delete().by { it.id }.execute(customWrapper)