api-docs / org.ktorm.support.sqlite / bulkInsertOrUpdateReturning

bulkInsertOrUpdateReturning

fun <T : BaseTable<*>, C : Any> Database.bulkInsertOrUpdateReturning(
    table: T,
    returning: Column<C>,
    block: BulkInsertOrUpdateStatementBuilder<T>.(T) -> Unit
): List<C?>

(source code)

Bulk insert records to the table, determining if there is a key conflict while inserting each of them,
automatically performs updates if any conflict exists, and finally returns the specific column.

Usage:

database.bulkInsertOrUpdateReturning(Employees, Employees.id) {
    item {
        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)
    }
    item {
        set(it.id, 5)
        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 + ?
returning id

Parameters

table - the table to be inserted.

returning - the column to return

block - the DSL block used to construct the expression.

Since
3.6.0

Return
the returning column’s values.

fun <T : BaseTable<*>, C1 : Any, C2 : Any> Database.bulkInsertOrUpdateReturning(
    table: T,
    returning: Pair<Column<C1>, Column<C2>>,
    block: BulkInsertOrUpdateStatementBuilder<T>.(T) -> Unit
): List<Pair<C1?, C2?>>

(source code)

Bulk insert records to the table, determining if there is a key conflict while inserting each of them,
automatically performs updates if any conflict exists, and finally returns the specific columns.

Usage:

database.bulkInsertOrUpdateReturning(Employees, Pair(Employees.id, Employees.job)) {
    item {
        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)
    }
    item {
        set(it.id, 5)
        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 + ?
returning id, job

Parameters

table - the table to be inserted.

returning - the columns to return

block - the DSL block used to construct the expression.

Since
3.6.0

Return
the returning columns’ values.

fun <T : BaseTable<*>, C1 : Any, C2 : Any, C3 : Any> Database.bulkInsertOrUpdateReturning(
    table: T,
    returning: Triple<Column<C1>, Column<C2>, Column<C3>>,
    block: BulkInsertOrUpdateStatementBuilder<T>.(T) -> Unit
): List<Triple<C1?, C2?, C3?>>

(source code)

Bulk insert records to the table, determining if there is a key conflict while inserting each of them,
automatically performs updates if any conflict exists, and finally returns the specific columns.

Usage:

database.bulkInsertOrUpdateReturning(Employees, Triple(Employees.id, Employees.job, Employees.salary)) {
    item {
        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)
    }
    item {
        set(it.id, 5)
        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 + ?
returning id, job, salary

Parameters

table - the table to be inserted.

returning - the columns to return

block - the DSL block used to construct the expression.

Since
3.6.0

Return
the returning columns’ values.