SqlUpdate

class SqlUpdate(table=None, alias=None)

Builds an UPDATE query. Created via SQL.update(table, alias). Inherits from SqlStatement (via SqlQuery).

Parameters:

table – Target table name string. Optional when used as an ON CONFLICT action.
alias – Optional table alias. Renders as UPDATE table AS alias.

In the examples below, qb refers to a SqlUpdate instance:

qb = SQL.update('films')

set(column, value=NOT_SPECIFIED)

Add a SET assignment. If value is omitted, column is treated as a literal expression (e.g. 'price = price * 1.10') or a SqlColumnList instance.

Parameters:

column – Column name string, a literal assignment string, or a SqlColumnList for column-list syntax.
value – The value — a Python object (creates a placemarker), a SqlObj for SQL expressions (rendered literally), or omitted for literal assignments and column lists.

column() is an alias for this method.

qb.set('status', 'active')
# sql:       SET status = %s
# args:      ['active']
# mogrified: SET status = 'active'

qb.set('counter', SQL.cat('counter + 1'))
# SET counter = counter + 1  -- no placemarker, literal SQL

qb.set('price = price * 1.10')
# SET price = price * 1.10   -- literal assignment string

qb.set(SQL.column_list('a', 'b').values(1, 2))
# SET (a, b) = (%s, %s)      -- column-list syntax

columns_kwargs(**kwargs)

Add multiple column/value pairs. Columns are sorted alphabetically.

Parameters:: kwargs – Keyword arguments where keys are column names and values are the corresponding values.

qb.columns_kwargs(name='Bob', age=25)
# SET
#     age = %s,
#     name = %s
# args: [25, 'Bob']

only()

Add ONLY keyword to restrict to the named table (no inherited tables).

qb.only()
# UPDATE ONLY parent_table

from_table(table, alias=None)

Add a FROM clause.

Parameters:

table – Table name string or SqlQuery subquery.
alias – Optional table alias.

qb.from_table('accounts')
# FROM accounts

qb.from_table('accounts', 'a')
# FROM accounts a

where(*expr)

Set the WHERE clause. Accepts either a single SqlObj (or None to clear), or positional args (expr, op, value) which are wrapped in a SqlTerm. See SqlTerm for the full list of supported parameter variants.

qb.where('id', '=', 42)
# sql:       WHERE id = %s
# args:      [42]
# mogrified: WHERE id = 42

qb.where('employees.id = accounts.sales_person')
# WHERE employees.id = accounts.sales_person

get_where()

Returns the current WHERE expression, or None.

Return type:: SqlObj or None

returning(*items)

Add a RETURNING clause.

Parameters:: items – One or more column name strings or SqlObj instances.

qb.returning('id', 'name')
# RETURNING id, name

returning_with(old='o', new='n')

Add RETURNING WITH (OLD/NEW) prefix (PostgreSQL 17+).

Parameters:

old – Alias for the OLD row. Default 'o'. Pass None to omit.
new – Alias for the NEW row. Default 'n'. Pass None to omit.

(SQL.update('products')
 .set('price = price * 1.10')
 .returning_with()
 .returning(SQL.column('o.price', 'old_price'),
            SQL.column('n.price', 'new_price')))
# RETURNING WITH (OLD AS o, NEW AS n)
#     o.price AS old_price, n.price AS new_price

recursive(): Mark the WITH clause as RECURSIVE.

with_cte(cte)

Add a SqlCte instance to the WITH clause.

Parameters:: cte – A SqlCte object (created via SQL.cte()).

qb.with_cte(SQL.cte('helper', helper_query))

with_cte_query(alias, query)

Shorthand for adding a CTE.

Parameters:

alias – CTE name string.
query – A SqlQuery (SQL.select(), SQL.insert(), SQL.update(), or SQL.delete() with RETURNING) for the CTE body.

qb.with_cte_query('totals', totals_query)

SqlColumnList

class SqlColumnList(*columns)

A column-list SET clause item for UPDATE queries. Created via SQL.column_list(*columns). Inherits from SqlObj.

Parameters:: columns – One or more column name strings.

SQL.column_list('a', 'b')

values(*values)

Set a value list. Values can be Python objects (parameterized), SqlObj expressions (rendered as SQL), or SqlParam instances.

SQL.column_list('a', 'b').values(1, 2)
# (a, b) = (%s, %s)   args: [1, 2]

SQL.column_list('a', 'b').values(SQL.cat('a + 1'), SQL.cat('DEFAULT'))
# (a, b) = (a + 1, DEFAULT)

query(query)

Set a sub-select as the value source.

Parameters:: query – A SqlSelect instance.

(SQL.column_list('first', 'last')
 .query(SQL.select()
        .columns('first_name', 'last_name')
        .from_table('employees')
        .where('employees.id = accounts.sales_person')))
# (first, last) = (
#     SELECT first_name, last_name
#     FROM employees
#     WHERE employees.id = accounts.sales_person
# )