ktorm-support-sqlite/org.ktorm.support.sqlite/insertOrUpdate

insertOrUpdate

fun <T : BaseTable<*>> Database.insertOrUpdate(table: T, block: InsertOrUpdateStatementBuilder.(T) -> Unit): Int(source)

Insert a record to the table, determining if there is a key conflict while it's being inserted, and automatically performs an update if any conflict exists.

Usage:

database.insertOrUpdate(Employees) {
    set(it.id, 1)
    set(it.name, "vince")
    set(it.job, "engineer")
    set(it.salary, 1000)
    set(it.hireDate, LocalDate.now())
    set(it.departmentId, 1)
    onConflict {
        set(it.salary, it.salary + 900)
    }
}

Generated SQL:

insert into t_employee (id, name, job, salary, hire_date, department_id)
values (?, ?, ?, ?, ?, ?)
on conflict (id) do update set salary = t_employee.salary + ?

By default, the column used in the on conflict statement is the primary key you already defined in the schema definition. If you want, you can specify one or more columns for the on conflict statement as belows:

database.insertOrUpdate(Employees) {
    set(it.id, 1)
    set(it.name, "vince")
    set(it.job, "engineer")
    set(it.salary, 1000)
    set(it.hireDate, LocalDate.now())
    set(it.departmentId, 1)
    onConflict(it.name, it.job) {
        set(it.salary, it.salary + 900)
    }
}

Generated SQL:

insert into t_employee (id, name, job, salary, hire_date, department_id)
values (?, ?, ?, ?, ?, ?)
on conflict (name, job) do update set salary = t_employee.salary + ?

Return

the effected row count.

Parameters

table

the table to be inserted.

block

the DSL block used to construct the expression.