Delete Statement

Basic Usage

The usage of the delete statement is:

Java

BookTable book = Tables.BOOK_TABLE;

int affectedRowCount = sqlClient
    .createDelete(book) 
    .where(book.name().eq("Learning GraphQL"))
    .execute();
System.out.println("Affected row count: " + affectedRowCount);

Kotlin

val affectedRowCount = sqlClient
    .createDelete(Book::class) {
        where(table.name eq "Learning GraphQL")
    }
    .execute() 
println("Affected row count: $affectedRowCount")

The generated SQL is:

delete  
from BOOK as tb_1_
where tb_1_.NAME = ?

Using JOIN

The delete statement usage below supports JOIN clauses, as follows:

Java

BookTable book = Tables.BOOK_TABLE;

int affectedRowCount = sqlClient
    .createDelete(book)
    .where(book.store().name().eq("MANNING")) 
    .execute();
System.out.println("Affected row count: " + affectedRowCount);

Kotlin

val affectedRowCount = sqlClient
    .createDelete(Book::class) {
        where(table.store.name eq "MANNING")
    }
    .execute()
println("Affected row count: $affectedRowCount") 

The finally generated SQL is:

1. select
       distinct tb_1_.ID
   from BOOK as tb_1_
   inner join BOOK_STORE as tb_2_
       on tb_1_.STORE_ID = tb_2_.ID
   where
       tb_2_.NAME = ? 

2. delete from BOOK_AUTHOR_MAPPING
   where BOOK_ID in(?, ?, ?) 

3. delete from BOOK
   where ID in(?, ?, ?)

note

If JOIN is used in a delete statement, Jimmer will translate it into select + delete. First use the select statement with join clause to query the ids of the data to be deleted, then use Delete Command to delete the data.

This scheme is valid for any database.