WheneableMergeQueryBuilder

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 {@link $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()

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()
}

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
}

Executes the query and returns an array of rows.

Also see the {@link executeTakeFirst} and {@link 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 {@link 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 {@link whenMatchedAnd}.

For a simple when not matched clause, see {@link whenNotMatched}.

For a when not matched clause with an and condition, see {@link whenNotMatchedAnd}.

Examples

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

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

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

For a simple when matched clause (without an and condition) see {@link whenMatched}.

Examples

const result = 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

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

This method is similar to {@link 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 {@link whenNotMatchedAnd}.

For a simple when matched clause, see {@link whenMatched}.

For a when matched clause with an and condition, see {@link whenMatchedAnd}.

Examples

const result = await db.mergeInto('person')
  .using('pet', 'person.id', 'pet.owner_id')
  .whenNotMatched()
  .thenInsertValues({
    first_name: 'John',
    last_name: 'Doe',
  })
  .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)

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

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

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

Unlike {@link 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()

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)

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

Unlike {@link whenMatchedAndRef}, you cannot reference columns from the target table.

This method is similar to {@link 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 {@link 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 {@link whenNotMatchedAnd}, but returns a MatchedThenableMergeQueryBuilder.

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

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