# UpsertStatement

open class UpsertStatement<Key : Any>(table: Table, val keys: Column<*>, val onUpdateExclude: List<Column<*>>?, val where: Op<Boolean>?) : InsertStatement<Key> , UpsertBuilder

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.

#### Parameters

table

Table to either insert values into or update values from.

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.

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. This clause may not be supported by all vendors.

## Constructors

UpsertStatement
Link copied to clipboard
constructor(table: Table, vararg keys: Column<*>, onUpdate: List<Pair<Column<*>, Expression<*>>>?, onUpdateExclude: List<Column<*>>?, where: Op<Boolean>?)
constructor(table: Table, vararg keys: Column<*>, onUpdateExclude: List<Column<*>>?, where: Op<Boolean>?)

## Properties

arguments
Link copied to clipboard
open var arguments: List<List<Pair<Column<*>, Any?>>>?
insertedCount
Link copied to clipboard
var insertedCount: Int

The number of rows affected by the insert operation.

isAlwaysBatch
Link copied to clipboard
open val isAlwaysBatch: Boolean

Whether the SQL statement is meant to be performed as part of a batch execution.

isIgnore
Link copied to clipboard
val isIgnore: Boolean
keys
Link copied to clipboard
val keys: Array<out Column<*>>
onUpdate
Link copied to clipboard
var onUpdate: List<Pair<Column<*>, Expression<*>>>?
onUpdateExclude
Link copied to clipboard
val onUpdateExclude: List<Column<*>>?
resultedValues
Link copied to clipboard
var resultedValues: List<ResultRow>?

The ResultRows generated by processing the database result set retrieved after executing the statement.

table
Link copied to clipboard
val table: Table
targets
Link copied to clipboard
val targets: List<Table>
type
Link copied to clipboard
val type: StatementType
where
Link copied to clipboard
val where: Op<Boolean>?

## Functions

arguments
Link copied to clipboard
open override fun arguments(): List<Iterable<Pair<IColumnType<*>, Any?>>>

Returns all mappings of columns and expression types to their values needed to prepare an SQL statement.

contains
Link copied to clipboard
open operator fun contains(column: Column<*>): Boolean
execute
Link copied to clipboard
fun execute(transaction: Transaction): Int?

Executes the SQL statement directly in the provided transaction and returns the generated result, or `null` if either no result was retrieved or if the transaction blocked statement execution.

executeInternal
Link copied to clipboard
open override fun PreparedStatementApi.executeInternal(transaction: Transaction): Int

Determines the exact way that an SQL statement is executed in a transaction and applies any necessary logic before returning the result generated by the executed statement.

get
Link copied to clipboard
infix operator fun <T> get(column: Column<T>): T
infix operator fun <T> get(column: CompositeColumn<T>): T
getOrNull
Link copied to clipboard
fun <T> getOrNull(column: Column<T>): T?

Returns the value of a given column from the first stored ResultRow, or `null` if either no results were retrieved from the database or if the column cannot be found in the row.

insertValue
Link copied to clipboard
open fun <T> insertValue(column: Column<T>): ExpressionWithColumnType<T>

Specifies that this column should be updated using the same values that would be inserted if there was no violation of a unique constraint in an upsert statement.

prepared
Link copied to clipboard
open override fun prepared(transaction: Transaction, sql: String): PreparedStatementApi

Uses a transaction connection and an sql string representation to return a precompiled SQL statement, stored as an implementation of PreparedStatementApi.

prepareSQL
Link copied to clipboard
open override fun prepareSQL(transaction: Transaction, prepared: Boolean): String

Returns the string representation of an SQL statement.

set
Link copied to clipboard
open operator fun <S> set(column: Column<S>, value: S)
open operator fun <T, S : T?, E : Expression<S>> set(column: Column<T>, value: E)
open operator fun <S> set(column: Column<S>, value: Query)
@JvmName(name = "setWithEntityIdExpression")
operator fun <S, ID : EntityID<S>, E : Expression<S>> set(column: Column<ID>, value: E)
@JvmName(name = "setWithNullableEntityIdValue")
operator fun <S : Any> set(column: Column<EntityID<S>?>, value: S?)
@JvmName(name = "setWithEntityIdValue")
operator fun <S : Any> set(column: Column<EntityID<S>>, value: S)
open operator fun <S> set(column: CompositeColumn<S>, value: S)
storeUpdateValues
Link copied to clipboard
open fun storeUpdateValues(onUpdate: UpsertBuilder.(UpdateStatement) -> Unit)

Calls the specified function onUpdate with an UpdateStatement as its argument, allowing values to be stored as part of the UPDATE clause of the upsert statement associated with this builder.

update
Link copied to clipboard
open fun <T, S : T?> update(column: Column<T>, value: SqlExpressionBuilder.() -> Expression<S>)

Updates the mapping of the specified column with the value of the provided expression.

open fun <T, S : T?> update(column: Column<T>, value: Expression<S>)

Updates the mapping of the specified column with the specified value if column has not been previously set up for a change and if value is of a valid type.