Skip to Content
📝 QueryingMerge Queries

Last Updated: 3/20/2026

Merge Queries

The MERGE statement (also known as “upsert” in some databases) combines insert, update, and delete operations into a single atomic statement. Kysely supports merge queries through the db.mergeInto() method, available in dialects that support the SQL standard MERGE syntax.

Basic Merge Structure

A merge query requires three components: a target table, a source table or query, and one or more conditional actions. Start with db.mergeInto() and specify the source with .using():

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

The generated SQL (PostgreSQL):

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

This deletes rows from person where a matching row exists in pet based on the join condition.

The Using Clause

The .using() method (defined in src/query-builder/merge-query-builder.ts) specifies the source table and join condition. It accepts the same syntax as .innerJoin():

// Simple join on two columns
await db
  .mergeInto('person')
  .using('pet', 'person.id', 'pet.owner_id')
  .whenMatched()
  .thenDelete()
  .execute()
 
// Join with a callback for complex conditions
await db
  .mergeInto('person')
  .using('pet', (join) => 
    join
      .onRef('person.id', '=', 'pet.owner_id')
      .on('pet.species', '=', 'dog')
  )
  .whenMatched()
  .thenDelete()
  .execute()

You can also use a subquery as the source:

await db
  .mergeInto('person')
  .using(
    db.selectFrom('pet')
      .select(['owner_id', 'name'])
      .where('species', '=', 'cat')
      .as('cat_owners'),
    'person.id',
    'cat_owners.owner_id'
  )
  .whenMatched()
  .thenUpdateSet({ has_cat: true })
  .execute()

When Matched Actions

After calling .using(), you get a WheneableMergeQueryBuilder that provides conditional action methods. The .whenMatched() method specifies actions to take when a row in the target table matches a row in the source:

// Delete matched rows
await db
  .mergeInto('person')
  .using('pet', 'person.id', 'pet.owner_id')
  .whenMatched()
  .thenDelete()
  .execute()
 
// Update matched rows
await db
  .mergeInto('person')
  .using('pet', 'person.id', 'pet.owner_id')
  .whenMatched()
  .thenUpdateSet({ has_pet: true })
  .execute()
 
// Do nothing for matched rows (PostgreSQL)
await db
  .mergeInto('person')
  .using('pet', 'person.id', 'pet.owner_id')
  .whenMatched()
  .thenDoNothing()
  .execute()

The MatchedThenableMergeQueryBuilder class (defined in src/query-builder/merge-query-builder.ts) provides three action methods:

  • thenDelete(): Delete the matched row from the target table
  • thenUpdateSet(updates): Update the matched row with the specified values
  • thenUpdate(callback): Update using a full UpdateQueryBuilder for complex updates
  • thenDoNothing(): Take no action (PostgreSQL only)

When Matched with Conditions

Add an AND condition to .whenMatched() using .whenMatchedAnd():

await db
  .mergeInto('person')
  .using('pet', 'person.id', 'pet.owner_id')
  .whenMatchedAnd('person.first_name', '=', 'John')
  .thenDelete()
  .execute()

The generated SQL (PostgreSQL):

merge into "person"
using "pet" on "person"."id" = "pet"."owner_id"
when matched and "person"."first_name" = $1 then delete

The condition can reference columns from both the target table and the source table. Use .whenMatchedAndRef() when both sides of the condition are column references:

await db
  .mergeInto('person')
  .using('pet', 'person.id', 'pet.owner_id')
  .whenMatchedAndRef('person.first_name', '=', 'pet.name')
  .thenDelete()
  .execute()

When Not Matched Actions

The .whenNotMatched() method specifies actions for rows in the source that don’t match any row in the target. Typically, this means inserting new rows:

await db
  .mergeInto('person')
  .using('pet', 'person.id', 'pet.owner_id')
  .whenNotMatched()
  .thenInsertValues({
    first_name: 'Unknown',
    last_name: 'Owner',
  })
  .execute()

The generated SQL (PostgreSQL):

merge into "person"
using "pet" on "person"."id" = "pet"."owner_id"
when not matched then
  insert ("first_name", "last_name") values ($1, $2)

The NotMatchedThenableMergeQueryBuilder class provides:

  • thenInsertValues(values): Insert a new row with the specified values
  • thenDoNothing(): Take no action (PostgreSQL only)

You can reference source table columns in the insert values:

await db
  .mergeInto('person')
  .using('pet', 'person.id', 'pet.owner_id')
  .whenNotMatched()
  .thenInsertValues((eb) => ({
    first_name: eb.ref('pet.name'),
    last_name: eb.val('(Pet Owner)'),
  }))
  .execute()

When Not Matched with Conditions

Add conditions to .whenNotMatched() using .whenNotMatchedAnd(). Note that you can only reference source table columns, not target table columns (since the target row doesn’t exist):

await db
  .mergeInto('person')
  .using('pet', 'person.id', 'pet.owner_id')
  .whenNotMatchedAnd('pet.species', '=', 'dog')
  .thenInsertValues({
    first_name: 'Dog',
    last_name: 'Owner',
  })
  .execute()

The generated SQL (PostgreSQL):

merge into "person"
using "pet" on "person"."id" = "pet"."owner_id"
when not matched and "pet"."species" = $1 then
  insert ("first_name", "last_name") values ($2, $3)

When Not Matched By Source

MS SQL Server supports a third condition type: WHEN NOT MATCHED BY SOURCE. This matches rows in the target table that have no corresponding row in the source:

await db
  .mergeInto('person')
  .using('pet', 'person.id', 'pet.owner_id')
  .whenNotMatchedBySource()
  .thenDelete()
  .execute()

This deletes people who don’t own any pets. The whenNotMatchedBySource() method returns a MatchedThenableMergeQueryBuilder (like whenMatched()) because you can reference target table columns.

Multiple When Clauses

Merge queries can have multiple WHEN clauses. Kysely evaluates them in the order you specify:

await db
  .mergeInto('person')
  .using('pet', 'person.id', 'pet.owner_id')
  .whenMatchedAnd('person.age', '<', 18)
  .thenUpdateSet({ guardian_required: true })
  .whenMatched()
  .thenUpdateSet({ has_pet: true })
  .whenNotMatchedAnd('pet.species', '=', 'dog')
  .thenInsertValues({
    first_name: 'Dog',
    last_name: 'Owner',
  })
  .whenNotMatched()
  .thenInsertValues({
    first_name: 'Pet',
    last_name: 'Owner',
  })
  .execute()

The generated SQL (PostgreSQL):

merge into "person"
using "pet" on "person"."id" = "pet"."owner_id"
when matched and "person"."age" < $1 then
  update set "guardian_required" = $2
when matched then
  update set "has_pet" = $3
when not matched and "pet"."species" = $4 then
  insert ("first_name", "last_name") values ($5, $6)
when not matched then
  insert ("first_name", "last_name") values ($7, $8)

Each row is processed by the first matching WHEN clause. Once a clause matches, subsequent clauses are skipped for that row.

Complex Updates

Use .thenUpdate() instead of .thenUpdateSet() when you need the full power of an UpdateQueryBuilder:

import { sql } from 'kysely'
 
await db
  .mergeInto('person')
  .using('pet', 'person.id', 'pet.owner_id')
  .whenMatched()
  .thenUpdate((ub) => ub
    .set('pet_count', (eb) => eb.fn.coalesce(eb.ref('pet_count'), eb.lit(0)) + 1)
    .set('updated_at', sql`now()`)
  )
  .execute()

The callback receives an UpdateQueryBuilder (without a table specified) and must return it after calling .set() one or more times.

Returning Results

Like insert, update, and delete queries, merge queries support .returning() and .returningAll() on dialects that support it:

const result = await db
  .mergeInto('person')
  .using('pet', 'person.id', 'pet.owner_id')
  .whenMatched()
  .thenUpdateSet({ has_pet: true })
  .whenNotMatched()
  .thenInsertValues({
    first_name: 'Unknown',
    last_name: 'Owner',
  })
  .returningAll()
  .execute()
 
// result is an array of person rows that were inserted or updated

On dialects that don’t support RETURNING, the result is an array containing a single MergeResult object with a numChangedRows property.

The MergeResult Type

When a merge query doesn’t use .returning(), it returns a MergeResult object (defined in src/query-builder/merge-result.ts):

interface MergeResult {
  readonly numChangedRows: bigint | undefined
}

The numChangedRows property contains the total number of rows inserted, updated, or deleted by the merge operation. Some dialects may not provide this information, in which case the value is undefined.

Dialect Support

Merge queries are supported in:

  • PostgreSQL (15+): Full support including DO NOTHING
  • MS SQL Server: Full support including WHEN NOT MATCHED BY SOURCE
  • Oracle: Full support
  • DB2: Full support

MySQL and SQLite do not support the standard MERGE syntax. Use INSERT ... ON DUPLICATE KEY UPDATE (MySQL) or INSERT ... ON CONFLICT (SQLite) instead.

What’s Next

  • CRUD Operations: Learn about standard insert, update, and delete operations.
  • Transactions: Ensure merge operations are atomic using transactions.
Window FunctionsMigrations