exposed-core/org.jetbrains.exposed.sql/upsert

upsert

fun <T : Table> T.upsert(vararg keys: Column<*>, onUpdate: UpsertBuilder.(UpdateStatement) -> Unit? = null, onUpdateExclude: List<Column<*>>? = null, where: SqlExpressionBuilder.() -> Op<Boolean>? = null, body: T.(UpsertStatement<Long>) -> Unit): UpsertStatement<Long>

Represents the SQL statement that either inserts a new row into a table, or updates the existing row if insertion would violate a unique constraint.

Note: Vendors that do not support this operation directly implement the standard MERGE USING command.

Note: Currently, the upsert() function might return an incorrect auto-generated ID (such as a UUID) if it performs an update. In this case, it returns a new auto-generated ID instead of the ID of the updated row. Postgres should not be affected by this issue as it implicitly returns the IDs of updated rows.

Parameters

keys

(optional) Columns to include in the condition that determines a unique constraint match. If no columns are provided, primary keys will be used. If the table does not have any primary keys, the first unique index will be attempted.

onUpdate

Lambda block with an UpdateStatement as its argument, allowing values to be assigned to the UPDATE clause. To specify manually that the insert value should be used when updating a column, for example within an expression or function, invoke insertValue() with the desired column as the function argument. If left null, all columns will be updated with the values provided for the insert.

onUpdateExclude

List of specific columns to exclude from updating. If left null, all columns will be updated with the values provided for the insert.

where

Condition that determines which rows to update, if a unique violation is found.

Samples

org.jetbrains.exposed.sql.tests.shared.dml.UpsertTests.testUpsertWithUniqueIndexConflict
fun <T : Table> T.upsert(vararg keys: Column<*>, onUpdate: List<Pair<Column<*>, Expression<*>>>, onUpdateExclude: List<Column<*>>? = null, where: SqlExpressionBuilder.() -> Op<Boolean>? = null, body: T.(UpsertStatement<Long>) -> Unit): UpsertStatement<Long>

Deprecated

This `upsert()` with `onUpdate` parameter that accepts a List will be removed in future releases. Please use `upsert()` with `onUpdate` parameter that takes an `UpdateStatement` lambda block instead.