Sets the values to update for an {@link Kysely.updateTable | update} query.

This method takes an object whose keys are column names and values are values to update. In addition to the column's type, the values can be any expressions such as raw sql snippets or select queries.

This method also accepts a callback that returns the update object. The callback takes an instance of ExpressionBuilder as its only argument. The expression builder can be used to create arbitrary update expressions.

The return value of an update query is an instance of UpdateResult. You can use the {@link returning} method on supported databases to get out the updated rows.

Examples

Update a row in person table:

const result = await db
  .updateTable('person')
  .set({
    first_name: 'Jennifer',
    last_name: 'Aniston'
  })
  .where('id', '=', '1')
  .executeTakeFirst()

console.log(result.numUpdatedRows)

The generated SQL (PostgreSQL):

update "person" set "first_name" = $1, "last_name" = $2 where "id" = $3

As always, you can provide a callback to the set method to get access to an expression builder:

const result = await db
  .updateTable('person')
  .set((eb) => ({
    age: eb('age', '+', 1),
    first_name: eb.selectFrom('pet').select('name').limit(1),
    last_name: 'updated',
  }))
  .where('id', '=', '1')
  .executeTakeFirst()

console.log(result.numUpdatedRows)

The generated SQL (PostgreSQL):

update "person"
set
  "first_name" = (select "name" from "pet" limit $1),
  "age" = "age" + $2,
  "last_name" = $3
where
  "id" = $4

If you provide two arguments the first one is interpreted as the column (or other target) and the second as the value:

const result = await db
  .updateTable('person')
  .set('first_name', 'Foo')
  // As always, both arguments can be arbitrary expressions or
  // callbacks that give you access to an expression builder:
  .set(sql`address['postalCode']`, (eb) => eb.val('61710))
  .where('id', '=', '1')
  .executeTakeFirst()

On PostgreSQL you can chain returning to the query to get the updated rows' columns (or any other expression) as the return value:

const row = await db
  .updateTable('person')
  .set({
    first_name: 'Jennifer',
    last_name: 'Aniston'
  })
  .where('id', '=', 1)
  .returning('id')
  .executeTakeFirstOrThrow()

row.id

The generated SQL (PostgreSQL):

update "person" set "first_name" = $1, "last_name" = $2 where "id" = $3 returning "id"

In addition to primitives, the values can arbitrary expressions including raw sql snippets or subqueries:

import { sql } from 'kysely'

const result = await db
  .updateTable('person')
  .set(({ selectFrom, ref, fn, eb }) => ({
    first_name: selectFrom('person').select('first_name').limit(1),
    middle_name: ref('first_name'),
    age: eb('age', '+', 1),
    last_name: sql`${'Ani'} || ${'ston'}`,
  }))
  .where('id', '=', 1)
  .executeTakeFirst()

console.log(result.numUpdatedRows)

The generated SQL (PostgreSQL):

update "person" set
"first_name" = (select "first_name" from "person" limit $1),
"middle_name" = "first_name",
"age" = "age" + $2,
"last_name" = $3 || $4
where "id" = $5