Class MergeQueryBuilder<DB, TT, O>

This can be used to add any additional SQL to the end of the query.

Examples

import { sql } from 'kysely'

await db
  .mergeInto('person')
  .using('pet', 'pet.owner_id', 'person.id')
  .whenMatched()
  .thenDelete()
  .modifyEnd(sql.raw('-- this is a comment'))
  .execute()
Copy

The generated SQL (PostgreSQL):

merge into "person" using "pet" on "pet"."owner_id" = "person"."id" when matched then delete -- this is a comment
Copy

Allows you to return data from modified rows.

On supported databases like MS SQL Server (MSSQL), this method can be chained to insert, update, delete and merge queries to return data.

Also see the outputAll method.

Examples

Return one column:

const { id } = await db
  .insertInto('person')
  .output('inserted.id')
  .values({
    first_name: 'Jennifer',
    last_name: 'Aniston',
    gender: 'female',
  })
  .executeTakeFirstOrThrow()
Copy

The generated SQL (MSSQL):

insert into "person" ("first_name", "last_name", "gender")
output "inserted"."id"
values (@1, @2, @3)
Copy

Return multiple columns:

const { old_first_name, old_last_name, new_first_name, new_last_name } = await db
  .updateTable('person')
  .set({ first_name: 'John', last_name: 'Doe' })
  .output([
    'deleted.first_name as old_first_name',
    'deleted.last_name as old_last_name',
    'inserted.first_name as new_first_name',
    'inserted.last_name as new_last_name',
  ])
  .where('created_at', '<', new Date())
  .executeTakeFirstOrThrow()
Copy

The generated SQL (MSSQL):

update "person"
set "first_name" = @1, "last_name" = @2
output "deleted"."first_name" as "old_first_name",
  "deleted"."last_name" as "old_last_name",
  "inserted"."first_name" as "new_first_name",
  "inserted"."last_name" as "new_last_name"
where "created_at" < @3
Copy

Return arbitrary expressions:

import { sql } from 'kysely'

const { full_name } = await db
  .deleteFrom('person')
  .output((eb) => sql<string>`concat(${eb.ref('deleted.first_name')}, ' ', ${eb.ref('deleted.last_name')})`.as('full_name'))
  .where('created_at', '<', new Date())
  .executeTakeFirstOrThrow()
Copy

The generated SQL (MSSQL):

delete from "person"
output concat("deleted"."first_name", ' ', "deleted"."last_name") as "full_name"
where "created_at" < @1
Copy

Return the action performed on the row:

await db
  .mergeInto('person')
  .using('pet', 'pet.owner_id', 'person.id')
  .whenMatched()
  .thenDelete()
  .whenNotMatched()
  .thenInsertValues({
    first_name: 'John',
    last_name: 'Doe',
    gender: 'male'
  })
  .output([
    'inserted.id as inserted_id',
    'deleted.id as deleted_id',
  ])
  .execute()
Copy

The generated SQL (MSSQL):

merge into "person"
using "pet" on "pet"."owner_id" = "person"."id"
when matched then delete
when not matched then
insert ("first_name", "last_name", "gender")
values (@1, @2, @3)
output "inserted"."id" as "inserted_id", "deleted"."id" as "deleted_id"
Copy

Adds an output {prefix}.* to an insert/update/delete/merge query on databases that support output such as MS SQL Server (MSSQL).

Also see the output method.

Allows you to return data from modified rows.

On supported databases like PostgreSQL, this method can be chained to insert, update, delete and merge queries to return data.

Note that on SQLite you need to give aliases for the expressions to avoid this bug in SQLite. For example .returning('id as id').

Also see the returningAll method.

Examples

Return one column:

const { id } = await db
  .insertInto('person')
  .values({
    first_name: 'Jennifer',
    last_name: 'Aniston'
  })
  .returning('id')
  .executeTakeFirstOrThrow()
Copy

Return multiple columns:

const { id, last_name } = await db
  .insertInto('person')
  .values({
    first_name: 'Jennifer',
    last_name: 'Aniston'
  })
  .returning(['id', 'last_name'])
  .executeTakeFirstOrThrow()
Copy

Return arbitrary expressions:

import { sql } from 'kysely'

const { id, full_name, first_pet_id } = await db
  .insertInto('person')
  .values({
    first_name: 'Jennifer',
    last_name: 'Aniston'
  })
  .returning((eb) => [
    'id as id',
    sql<string>`concat(first_name, ' ', last_name)`.as('full_name'),
    eb.selectFrom('pet').select('pet.id').limit(1).as('first_pet_id')
  ])
  .executeTakeFirstOrThrow()
Copy

Adds a returning * or returning table.* to an insert/update/delete/merge query on databases that support returning such as PostgreSQL.

Also see the returning method.

Adds a returning * to an insert/update/delete/merge query on databases that support returning such as PostgreSQL.

Also see the returning method.

Changes a merge into query to an merge top into query.

top clause is only supported by some dialects like MS SQL Server.

Examples

Affect 5 matched rows at most:

await db.mergeInto('person')
  .top(5)
  .using('pet', 'person.id', 'pet.owner_id')
  .whenMatched()
  .thenDelete()
  .execute()
Copy

The generated SQL (MS SQL Server):

merge top(5) into "person"
using "pet" on "person"."id" = "pet"."owner_id"
when matched then
  delete
Copy

Affect 50% of matched rows:

await db.mergeInto('person')
  .top(50, 'percent')
  .using('pet', 'person.id', 'pet.owner_id')
  .whenMatched()
  .thenDelete()
  .execute()
Copy

The generated SQL (MS SQL Server):

merge top(50) percent into "person"
using "pet" on "person"."id" = "pet"."owner_id"
when matched then
  delete
Copy

Adds the using clause to the query.

This method is similar to SelectQueryBuilder.innerJoin, so see the documentation for that method for more examples.

Examples

const result = await db.mergeInto('person')
  .using('pet', 'person.id', 'pet.owner_id')
  .whenMatched()
  .thenDelete()
  .execute()
Copy

The generated SQL (PostgreSQL):

merge into "person"
using "pet" on "person"."id" = "pet"."owner_id"
when matched then
  delete
Copy

Adds the using clause to the query.

This method is similar to SelectQueryBuilder.innerJoin, so see the documentation for that method for more examples.

Examples

const result = await db.mergeInto('person')
  .using('pet', 'person.id', 'pet.owner_id')
  .whenMatched()
  .thenDelete()
  .execute()
Copy

The generated SQL (PostgreSQL):

merge into "person"
using "pet" on "person"."id" = "pet"."owner_id"
when matched then
  delete
Copy