UPDATE Queries

Builds UPDATE queries using SqlUpdate.

All SQL output below shows the result of get_sql(). Where queries have parameters, get_args() is shown alongside.

Basic Update

The .where() method accepts the same parameter variants as SqlTerm.

qb = (SQL.update('films')
      .set('kind', 'Dramatic')
      .where('kind', '=', 'Drama'))

UPDATE films
SET kind = %s
WHERE kind = %s

-- get_args(): ['Dramatic', 'Drama']

Multiple Assignments

Individual columns with literal SQL expressions:

qb = (SQL.update('weather')
      .set('temp_lo = temp_lo + 1')
      .set('temp_hi = temp_lo + 15')
      .set('prcp = DEFAULT'))

UPDATE weather
SET
    temp_lo = temp_lo + 1,
    temp_hi = temp_lo + 15,
    prcp = DEFAULT

With parameterized values:

qb = (SQL.update('weather')
      .set('temp_lo', SQL.cat('temp_lo + ', SQL.value(1)))
      .set('temp_hi', SQL.cat('temp_lo + ', SQL.value(15))))

UPDATE weather
SET
    temp_lo = temp_lo + %s,
    temp_hi = temp_lo + %s

-- get_args(): [1, 15]

Using columns_kwargs() for keyword-style:

qb = (SQL.update('users')
      .columns_kwargs(name='Bob', age=25))

UPDATE users
SET
    age = %s,
    name = %s

-- get_args(): [25, 'Bob']

Column-List Syntax

Use SQL.column_list() to update multiple columns as a group:

qb = (SQL.update('weather')
      .set(SQL.column_list('temp_lo', 'temp_hi', 'prcp')
           .values(SQL.cat('temp_lo + 1'), SQL.cat('temp_lo + 15'), SQL.cat('DEFAULT')))
      .where(SQL.and_expr()
             .add('city', '=', 'San Francisco')
             .add('date', '=', '2003-07-03')))

UPDATE weather
SET (temp_lo, temp_hi, prcp) = (temp_lo + 1, temp_lo + 15, DEFAULT)
WHERE
    city = %s
    AND date = %s

-- get_args(): ['San Francisco', '2003-07-03']

With a subquery:

qb = (SQL.update('accounts')
      .set(SQL.column_list('contact_first_name', 'contact_last_name')
           .query(SQL.select()
                  .columns('first_name', 'last_name')
                  .from_table('employees')
                  .where('employees.id = accounts.sales_person'))))

UPDATE accounts
SET
    (contact_first_name, contact_last_name) = (
        SELECT first_name, last_name
        FROM employees
        WHERE employees.id = accounts.sales_person
    )

Column lists can be mixed freely with single column assignments:

qb = (SQL.update('t')
      .set('a', 1)
      .set(SQL.column_list('b', 'c').values(SQL.cat('2'), SQL.cat('3')))
      .set('d', 4))

UPDATE t
SET
    a = %s,
    (b, c) = (2, 3),
    d = %s

-- get_args(): [1, 4]

Multiple column lists are also supported:

qb = (SQL.update('t')
      .set(SQL.column_list('a', 'b').values(1, 2))
      .set(SQL.column_list('c', 'd').values(3, 4)))

UPDATE t
SET
    (a, b) = (%s, %s),
    (c, d) = (%s, %s)

-- get_args(): [1, 2, 3, 4]

FROM Clause

qb = (SQL.update('employees')
      .set('sales_count', SQL.cat('sales_count + ', SQL.value(1)))
      .from_table('accounts')
      .where(SQL.and_expr()
             .add('accounts.name', '=', 'Acme Corporation')
             .add('employees.id = accounts.sales_person')))

UPDATE employees
SET sales_count = sales_count + %s
FROM accounts
WHERE
    accounts.name = %s
    AND employees.id = accounts.sales_person

-- get_args(): [1, 'Acme Corporation']

UPDATE ONLY

PostgreSQL only.

Restrict update to the named table only (no inherited tables):

qb = (SQL.update('parent_table')
      .only()
      .set('status', 'archived'))

UPDATE ONLY parent_table
SET status = %s

-- get_args(): ['archived']

WITH Clause

qb = (SQL.update('employees')
      .recursive()
      .with_cte_query('subordinates', recursive_select)
      .set('department', 'Engineering')
      .from_table('subordinates')
      .where('employees.id = subordinates.id'))

WITH RECURSIVE subordinates AS (
    ...
)
UPDATE employees
SET department = %s
FROM subordinates
WHERE employees.id = subordinates.id

-- get_args(): ['Engineering']

RETURNING

PostgreSQL only.

qb = (SQL.update('products')
      .set('price = price * 1.10')
      .returning('id', 'price'))

UPDATE products
SET price = price * 1.10
RETURNING id, price

With OLD/NEW references (PostgreSQL 17+):

qb = (SQL.update('products')
      .set('price = price * 1.10')
      .returning_with(old='o', new='n')
      .returning(SQL.column('o.price', 'old_price'),
                 SQL.column('n.price', 'new_price')))

UPDATE products
SET price = price * 1.10
RETURNING WITH (OLD AS o, NEW AS n) o.price AS old_price, n.price AS new_price