import { kysely } from "https://dotland.deno.dev/x/kysely_postgrs_js_dialect@v0.27.4/mod.ts";
const { UpdateQueryBuilder } = kysely;
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