Edit

Update Queries

Reference: Postgres, SQLite, MySQL, T-SQL, Oracle

Overview

Set

.from specifies the table to modify and .set specifies the fields to update. .from works it does in Delete queries.

sq.from`person`
  .set`age = age + 1, name = ${'Sally'}`
  .query

{ text: 'update person set age = age + 1, name = $1',
  args: ['Sally'] }

Multiple calls to .set are joined with ', '.

sq.from`person`
  .set`age = age + 1`
  .set`name = ${'Sally'}`
  .query

{ text: 'update person set age = age + 1, name = $1',
  args: ['Sally'] }

.set accepts objects in the form { field: value }. Each property generates a field = value clause.

sq.from('person')
  .set(
    { firstName: 'Robert', nickname: 'Rob' },
    { processed: true }
  )
  .query

{ text: 'update person set first_name = $1, nickname = $2, processed = $3',
  args: ['Robert', 'Rob', true] }

Values can be Expressions.

sq.from('person')
  .set({ age: e.add(3, 4) })
  .query

{ text: 'update person set age = ($1 + $2)',
  args: [3, 4] }

Values can be Fragments.

sq.from('person')
  .set({ age: sq.txt`3 + 4` })
  .query

{ text: 'update person set age = 3 + 4',
  args: [] }

Values can be Subqueries.

sq.from('person')
  .set({
    firstName: sq.sql`select 'Bob'`,
    lastName: sq.return`'Smith'`
  })
 .query

{ text: "update person set first_name = (select 'Bob'), last_name = (select 'Smith')",
  args: [] }

Where

Filter the rows to update with .where.

sq.from`person`
  .where({ firstName: 'Matt' })
  .set({ firstName: 'Robert', nickname: 'Rob' })
  .query

{ text: 'update person set first_name = $1, nickname = $2 where (first_name = $3)',
  args: ['Robert', 'Rob', 'Matt'] }

.where works it does in Select queries.

From

Postgres Only: The first .from call forms the update clause. Subsequent .from calls form the from clause.

sq.from`book`
  .from`author`
  .set({ available: false })
  .where`book.author_id = author.id and author.contract = 'terminated'`
  .query

{ text: "update book set available = $1 from author where (book.author_id = author.id and author.contract = 'terminated')",
  args: [false] }

Returning

Postgres Only: Return the updated rows with .return.

sq.from`person`
  .where`age > 60 and old = false`
  .set`old = true`
  .return`id, age`
  .query

{ text: 'update person set old = true where (age > 60 and old = false) returning id, age',
  args: [] }

Express

Express syntax works.

sq`person`({ firstName: 'Rob' })`id`.set({ firstName: 'Robert'}).query

{ text: 'update person set first_name = $1 where first_name = $2 returning id',
  args: ['Robert', 'Rob'] }
← InsertExpressions →