Class WheneableMergeQueryBuilder<DB, TT, ST, O>

Simply calls the provided function passing this as the only argument. $call returns what the provided function returns.

If you want to conditionally call a method on this, see the $if method.

Examples

The next example uses a helper function log to log a query:

function log<T extends Compilable>(qb: T): T {
  console.log(qb.compile())
  return qb
}

db.updateTable('person')
  .set(values)
  .$call(log)
  .execute()
Copy

Call func(this) if condition is true.

This method is especially handy with optional selects. Any returning or returningAll method calls add columns as optional fields to the output type when called inside the func callback. This is because we can't know if those selections were actually made before running the code.

You can also call any other methods inside the callback.

Examples

async function updatePerson(id: number, updates: UpdateablePerson, returnLastName: boolean) {
  return await db
    .updateTable('person')
    .set(updates)
    .where('id', '=', id)
    .returning(['id', 'first_name'])
    .$if(returnLastName, (qb) => qb.returning('last_name'))
    .executeTakeFirstOrThrow()
}
Copy

Any selections added inside the if callback will be added as optional fields to the output type since we can't know if the selections were actually made before running the code. In the example above the return type of the updatePerson function is:

{
  id: number
  first_name: string
  last_name?: string
}
Copy

Executes the query and returns an array of rows.

Also see the executeTakeFirst and executeTakeFirstOrThrow methods.

Executes the query and returns the first result or undefined if the query returned no result.

Executes the query and returns the first result or throws if the query returned no result.

By default an instance of NoResultError is thrown, but you can provide a custom error class, or callback as the only argument to throw a different error.

See MergeQueryBuilder.top.

Adds a simple when matched clause to the query.

For a when matched clause with an and condition, see whenMatchedAnd.

For a simple when not matched clause, see whenNotMatched.

For a when not matched clause with an and condition, see whenNotMatchedAnd.

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 when matched clause to the query with an and condition.

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

For a simple when matched clause (without an and condition) see whenMatched.

Examples

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

The generated SQL (PostgreSQL):

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

Adds the when matched clause to the query with an and condition. But unlike whenMatchedAnd, this method accepts a column reference as the 3rd argument.

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

Adds a simple when not matched clause to the query.

For a when not matched clause with an and condition, see whenNotMatchedAnd.

For a simple when matched clause, see whenMatched.

For a when matched clause with an and condition, see whenMatchedAnd.

Examples

const result = await db.mergeInto('person')
  .using('pet', 'person.id', 'pet.owner_id')
  .whenNotMatched()
  .thenInsertValues({
    first_name: 'John',
    last_name: 'Doe',
  })
  .execute()
Copy

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)
Copy

Adds the when not matched clause to the query with an and condition.

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

For a simple when not matched clause (without an and condition) see whenNotMatched.

Unlike whenMatchedAnd, you cannot reference columns from the table merged into.

Examples

const result = await db.mergeInto('person')
  .using('pet', 'person.id', 'pet.owner_id')
  .whenNotMatchedAnd('pet.name', '=', 'Lucky')
  .thenInsertValues({
    first_name: 'John',
    last_name: 'Doe',
  })
  .execute()
Copy

The generated SQL (PostgreSQL):

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

Adds the when not matched clause to the query with an and condition. But unlike whenNotMatchedAnd, this method accepts a column reference as the 3rd argument.

Unlike whenMatchedAndRef, you cannot reference columns from the target table.

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

Adds a simple when not matched by source clause to the query.

Supported in MS SQL Server.

Similar to whenNotMatched, but returns a MatchedThenableMergeQueryBuilder.

Adds the when not matched by source clause to the query with an and condition.

Supported in MS SQL Server.

Similar to whenNotMatchedAnd, but returns a MatchedThenableMergeQueryBuilder.

Adds the when not matched by source clause to the query with an and condition.

Similar to whenNotMatchedAndRef, but you can reference columns from the target table, and not from source table and returns a MatchedThenableMergeQueryBuilder.