This page is part of the .

Previous: Writing SELECT statements for Inheritance Mappings | Next:

ORM-Enabled INSERT, UPDATE, and DELETE statements

About this Document

This section makes use of ORM mappings first illustrated in the , shown in the section Declaring Mapped Classes, as well as inheritance mappings shown in the section .

View the ORM setup for this page.

The method, in addition to handling ORM-enabled Select objects, can also accommodate ORM-enabled , Update and objects, in various ways which are each used to INSERT, UPDATE, or DELETE many database rows at once. There is also dialect-specific support for ORM-enabled “upserts”, which are INSERT statements that automatically make use of UPDATE for rows that already exist.

The following table summarizes the calling forms that are discussed in this document:

A construct can be constructed in terms of an ORM class and passed to the Session.execute() method. A list of parameter dictionaries sent to the parameter, separate from the Insert object itself, will invoke bulk INSERT mode for the statement, which essentially means the operation will optimize as much as possible for many rows:

The parameter dictionaries contain key/value pairs which may correspond to ORM mapped attributes that line up with mapped or mapped_column() declarations, as well as with declarations. The keys should match the ORM mapped attribute name and not the actual database column name, if these two names happen to be different.

Changed in version 2.0: Passing an Insert construct to the method now invokes a “bulk insert”, which makes use of the same functionality as the legacy Session.bulk_insert_mappings() method. This is a behavior change compared to the 1.x series where the would be interpreted in a Core-centric way, using column names for value keys; ORM attribute keys are now accepted. Core-style functionality is available by passing the execution option to the Session.execution_options parameter of .

The bulk ORM insert feature supports INSERT..RETURNING for selected backends, which can return a object that may yield individual columns back as well as fully constructed ORM objects corresponding to the new rows. INSERT..RETURNING requires the use of a backend that supports SQL RETURNING syntax as well as support for executemany with RETURNING; this feature is available with all backends with the exception of MySQL (MariaDB is included).

As an example, we can run the same statement as before, adding use of the UpdateBase.returning() method, passing the full User entity as what we’d like to return. is used to allow iteration of User objects:

>>> users = session.scalars(
...     insert(User).returning(User),
...     [
...         {"name": "spongebob", "fullname": "Spongebob Squarepants"},
...         {"name": "sandy", "fullname": "Sandy Cheeks"},
...         {"name": "patrick", "fullname": "Patrick Star"},
...         {"name": "squidward", "fullname": "Squidward Tentacles"},
...         {"name": "ehkrabs", "fullname": "Eugene H. Krabs"},
...     ],
... )
INSERT INTO user_account (name, fullname)
VALUES (?, ?), (?, ?), (?, ?), (?, ?), (?, ?) RETURNING id, name, fullname, species
[... (insertmanyvalues)] ('spongebob', 'Spongebob Squarepants', 'sandy',
'Sandy Cheeks', 'patrick', 'Patrick Star', 'squidward', 'Squidward Tentacles',
'ehkrabs', 'Eugene H. Krabs')
>>> print(users.all())
[User(name='spongebob', fullname='Spongebob Squarepants'),
 User(name='sandy', fullname='Sandy Cheeks'),
 User(name='patrick', fullname='Patrick Star'),
 User(name='squidward', fullname='Squidward Tentacles'),
 User(name='ehkrabs', fullname='Eugene H. Krabs')]

In the above example, the rendered SQL takes on the form used by the insertmanyvalues feature as requested by the SQLite backend, where individual parameter dictionaries are inlined into a single INSERT statement so that RETURNING may be used.

Changed in version 2.0: The ORM now interprets RETURNING clauses from Insert, , and even Delete constructs in an ORM context, meaning a mixture of column expressions and ORM mapped entities may be passed to the method which will then be delivered in the way that ORM results are delivered from constructs such as Select, including that mapped entities will be delivered in the result as ORM mapped objects. Limited support for ORM loader options such as and selectinload() is also present.

Using Heterogenous Parameter Dictionaries

The ORM bulk insert feature supports lists of parameter dictionaries that are “heterogenous”, which basically means “individual dictionaries can have different keys”. When this condition is detected, the ORM will break up the parameter dictionaries into groups corresponding to each set of keys and batch accordingly into separate INSERT statements:

>>> users = session.scalars(
...     insert(User).returning(User),
...     [
...         {
...             "name": "spongebob",
...             "fullname": "Spongebob Squarepants",
...             "species": "Sea Sponge",
...         },
...         {"name": "sandy", "fullname": "Sandy Cheeks", "species": "Squirrel"},
...         {"name": "patrick", "species": "Starfish"},
...         {
...             "name": "squidward",
...             "fullname": "Squidward Tentacles",
...             "species": "Squid",
...         },
...         {"name": "ehkrabs", "fullname": "Eugene H. Krabs", "species": "Crab"},
...     ],
... )
INSERT INTO user_account (name, fullname, species) VALUES (?, ?, ?), (?, ?, ?) RETURNING id, name, fullname, species
[... (insertmanyvalues)] ('spongebob', 'Spongebob Squarepants', 'Sea Sponge', 'sandy', 'Sandy Cheeks', 'Squirrel')
INSERT INTO user_account (name, species) VALUES (?, ?) RETURNING id, name, fullname, species
[...] ('patrick', 'Starfish')
INSERT INTO user_account (name, fullname, species) VALUES (?, ?, ?), (?, ?, ?) RETURNING id, name, fullname, species
[... (insertmanyvalues)] ('squidward', 'Squidward Tentacles', 'Squid', 'ehkrabs', 'Eugene H. Krabs', 'Crab')

In the above example, the five parameter dictionaries passed translated into three INSERT statements, grouped along the specific sets of keys in each dictionary while still maintaining row order, i.e. ("name", "fullname", "species"), ("name", "species"), ("name","fullname", "species").

Bulk INSERT for Joined Table Inheritance

ORM bulk insert builds upon the internal system that is used by the traditional system in order to emit INSERT statements. This means that for an ORM entity that is mapped to multiple tables, typically one which is mapped using joined table inheritance, the bulk INSERT operation will emit an INSERT statement for each table represented by the mapping, correctly transferring server-generated primary key values to the table rows that depend upon them. The RETURNING feature is also supported here, where the ORM will receive objects for each INSERT statement executed, and will then “horizontally splice” them together so that the returned rows include values for all columns inserted:

>>> managers = session.scalars(
...     insert(Manager).returning(Manager),
...     [
...         {"name": "sandy", "manager_name": "Sandy Cheeks"},
...         {"name": "ehkrabs", "manager_name": "Eugene H. Krabs"},
...     ],
... )
INSERT INTO employee (name, type) VALUES (?, ?), (?, ?) RETURNING id, name, type
[... (insertmanyvalues)] ('sandy', 'manager', 'ehkrabs', 'manager')
INSERT INTO manager (id, manager_name) VALUES (?, ?), (?, ?) RETURNING id, manager_name
[... (insertmanyvalues)] (1, 'Sandy Cheeks', 2, 'Eugene H. Krabs')
>>> print(managers.all())
[Manager('sandy', manager_name='Sandy Cheeks'), Manager('ehkrabs', manager_name='Eugene H. Krabs')]

ORM Bulk Insert with SQL Expressions

The ORM bulk insert feature supports the addition of a fixed set of parameters which may include SQL expressions to be applied to every target row. To achieve this, combine the use of the method, passing a dictionary of parameters that will be applied to all rows, with the usual bulk calling form by including a list of parameter dictionaries that contain individual row values when invoking Session.execute().

As an example, given an ORM mapping that includes a “timestamp” column:

import datetime
class LogRecord(Base):
    __tablename__ = "log_record"
    id: Mapped[int] = mapped_column(primary_key=True)
    message: Mapped[str]
    code: Mapped[str]
    timestamp: Mapped[datetime.datetime]

If we wanted to INSERT a series of LogRecord elements, each with a unique message field, however we would like to apply the SQL function now() to all rows, we can pass timestamp within and then pass the additional records using “bulk” mode:

>>> from sqlalchemy import func
>>> log_record_result = session.scalars(
...     insert(LogRecord).values(code="SQLA", timestamp=func.now()).returning(LogRecord),
...     [
...         {"message": "log message #1"},
...         {"message": "log message #2"},
...         {"message": "log message #3"},
...         {"message": "log message #4"},
...     ],
... )
INSERT INTO log_record (message, code, timestamp)
VALUES (?, ?, CURRENT_TIMESTAMP), (?, ?, CURRENT_TIMESTAMP), (?, ?, CURRENT_TIMESTAMP),
(?, ?, CURRENT_TIMESTAMP)
RETURNING id, message, code, timestamp
[... (insertmanyvalues)] ('log message #1', 'SQLA', 'log message #2', 'SQLA',
'log message #3', 'SQLA', 'log message #4', 'SQLA')
>>> print(log_record_result.all())
[LogRecord('log message #1', 'SQLA', datetime.datetime(...)),
 LogRecord('log message #2', 'SQLA', datetime.datetime(...)),
 LogRecord('log message #3', 'SQLA', datetime.datetime(...)),
 LogRecord('log message #4', 'SQLA', datetime.datetime(...))]

ORM Bulk Insert with Per Row SQL Expressions

The method itself accommodates a list of parameter dictionaries directly. When using the Insert construct in this way, without passing any list of parameter dictionaries to the parameter, bulk ORM insert mode is not used, and instead the INSERT statement is rendered exactly as given and invoked exactly once. This mode of operation may be useful both for the case of passing SQL expressions on a per-row basis, and is also used when using “upsert” statements with the ORM, documented later in this chapter at ORM “upsert” Statements.

A contrived example of an INSERT that embeds per-row SQL expressions, and also demonstrates in this form, is below:

>>> from sqlalchemy import select
...     insert(Address)
...     .values(
...         [
...             {
...                 "user_id": select(User.id).where(User.name == "sandy"),
...                 "email_address": "sandy@company.com",
...             },
...                 "user_id": select(User.id).where(User.name == "spongebob"),
...                 "email_address": "spongebob@company.com",
...             },
...             {
...                 "user_id": select(User.id).where(User.name == "patrick"),
...                 "email_address": "patrick@company.com",
...             },
...         ]
...     )
...     .returning(Address),
... )
INSERT INTO address (user_id, email_address) VALUES
((SELECT user_account.id
FROM user_account
WHERE user_account.name = ?), ?), ((SELECT user_account.id
FROM user_account
WHERE user_account.name = ?), ?), ((SELECT user_account.id
FROM user_account
WHERE user_account.name = ?), ?) RETURNING id, user_id, email_address
[...] ('sandy', 'sandy@company.com', 'spongebob', 'spongebob@company.com',
'patrick', 'patrick@company.com')
>>> print(address_result.all())
[Address(email_address='sandy@company.com'),
 Address(email_address='spongebob@company.com'),
 Address(email_address='patrick@company.com')]

Because bulk ORM insert mode is not used above, the following features are not present:

Joined table inheritance or other multi-table mappings are not supported, since that would require multiple INSERT statements.
are not supported - each element in the VALUES set must have the same columns.
Core-level scale optimizations such as the batching provided by insertmanyvalues are not available; statements will need to ensure the total number of parameters does not exceed limits imposed by the backing database.

For the above reasons, it is generally not recommended to use multiple parameter sets with with ORM INSERT statements unless there is a clear rationale, which is either that “upsert” is being used or there is a need to embed per-row SQL expressions in each parameter set.

See also

ORM “upsert” Statements

The Session includes legacy methods for performing “bulk” INSERT and UPDATE statements. These methods share implementations with the SQLAlchemy 2.0 versions of these features, described at and ORM Bulk UPDATE by Primary Key, however lack many features, namely RETURNING support as well as support for session-synchronization.

Code which makes use of for example can port code as follows, starting with this mappings example:

session.bulk_insert_mappings(User, [{"name": "u1"}, {"name": "u2"}, {"name": "u3"}])

The above is expressed using the new API as:

See also

Legacy Session Bulk UPDATE Methods

ORM “upsert” Statements

Selected backends with SQLAlchemy may include dialect-specific Insert constructs which additionally have the ability to perform “upserts”, or INSERTs where an existing row in the parameter set is turned into an approximation of an UPDATE statement instead. By “existing row” , this may mean rows which share the same primary key value, or may refer to other indexed columns within the row that are considered to be unique; this is dependent on the capabilities of the backend in use.

The dialects included with SQLAlchemy that include dialect-specific “upsert” API features are:

SQLite - using documented at INSERT…ON CONFLICT (Upsert)
PostgreSQL - using documented at INSERT…ON CONFLICT (Upsert)
MySQL/MariaDB - using documented at INSERT…ON DUPLICATE KEY UPDATE (Upsert)

Users should review the above sections for background on proper construction of these objects; in particular, the “upsert” method typically needs to refer back to the original statement, so the statement is usually constructed in two separate steps.

Third party backends such as those mentioned at may also feature similar constructs.

While SQLAlchemy does not yet have a backend-agnostic upsert construct, the above Insert variants are nonetheless ORM compatible in that they may be used in the same way as the construct itself as documented at ORM Bulk Insert with Per Row SQL Expressions, that is, by embedding the desired rows to INSERT within the method. In the example below, the SQLite insert() function is used to generate an construct that includes “ON CONFLICT DO UPDATE” support. The statement is then passed to Session.execute() where it proceeds normally, with the additional characteristic that the parameter dictionaries passed to are interpreted as ORM mapped attribute keys, rather than column names:

>>> from sqlalchemy.dialects.sqlite import insert as sqlite_upsert
>>> stmt = sqlite_upsert(User).values(
...     [
...         {"name": "spongebob", "fullname": "Spongebob Squarepants"},
...         {"name": "sandy", "fullname": "Sandy Cheeks"},
...         {"name": "patrick", "fullname": "Patrick Star"},
...         {"name": "squidward", "fullname": "Squidward Tentacles"},
...         {"name": "ehkrabs", "fullname": "Eugene H. Krabs"},
...     ]
... )
>>> stmt = stmt.on_conflict_do_update(
...     index_elements=[User.name], set_=dict(fullname=stmt.excluded.fullname)
... )
>>> session.execute(stmt)
INSERT INTO user_account (name, fullname)
VALUES (?, ?), (?, ?), (?, ?), (?, ?), (?, ?)
ON CONFLICT (name) DO UPDATE SET fullname = excluded.fullname
[...] ('spongebob', 'Spongebob Squarepants', 'sandy', 'Sandy Cheeks',
'patrick', 'Patrick Star', 'squidward', 'Squidward Tentacles',
'ehkrabs', 'Eugene H. Krabs')
<...>

Using RETURNING with upsert statements

From the SQLAlchemy ORM’s point of view, upsert statements look like regular constructs, which includes that Insert.returning() works with upsert statements in the same way as was demonstrated at , so that any column expression or relevant ORM entity class may be passed. Continuing from the example in the previous section:

>>> result = session.scalars(
...     stmt.returning(User), execution_options={"populate_existing": True}
... )
INSERT INTO user_account (name, fullname)
VALUES (?, ?), (?, ?), (?, ?), (?, ?), (?, ?)
ON CONFLICT (name) DO UPDATE SET fullname = excluded.fullname
RETURNING id, name, fullname, species
[...] ('spongebob', 'Spongebob Squarepants', 'sandy', 'Sandy Cheeks',
'patrick', 'Patrick Star', 'squidward', 'Squidward Tentacles',
'ehkrabs', 'Eugene H. Krabs')
>>> print(result.all())
[User(name='spongebob', fullname='Spongebob Squarepants'),
  User(name='sandy', fullname='Sandy Cheeks'),
  User(name='patrick', fullname='Patrick Star'),
  User(name='squidward', fullname='Squidward Tentacles'),
  User(name='ehkrabs', fullname='Eugene H. Krabs')]

The example above uses RETURNING to return ORM objects for each row inserted or upserted by the statement. The example also adds use of the Populate Existing execution option. This option indicates that User objects which are already present in the for rows that already exist should be refreshed with the data from the new row. For a pure Insert statement, this option is not significant, because every row produced is a brand new primary key identity. However when the also includes “upsert” options, it may also be yielding results from rows that already exist and therefore may already have a primary key identity represented in the Session object’s .

See also

Populate Existing

The Update construct may be used with in a similar way as the Insert statement is used as described at , passing a list of many parameter dictionaries, each dictionary representing an individual row that corresponds to a single primary key value. This use should not be confused with a more common way to use Update statements with the ORM, using an explicit WHERE clause, which is documented at .

For the “bulk” version of UPDATE, a update() construct is made in terms of an ORM class and passed to the method; the resulting Update object should have no WHERE criteria or values, that is, the and Update.values() methods are not used. Passing the construct along with a list of parameter dictionaries which each include a full primary key value will invoke bulk UPDATE by primary key mode for the statement, generating the appropriate WHERE criteria to match each row by primary key, and using executemany to run each parameter set against the UPDATE statement:

>>> from sqlalchemy import update
>>> session.execute(
...     update(User),
...     [
...         {"id": 1, "fullname": "Spongebob Squarepants"},
...         {"id": 3, "fullname": "Patrick Star"},
...         {"id": 5, "fullname": "Eugene H. Krabs"},
...     ],
... )
UPDATE user_account SET fullname=? WHERE user_account.id = ?
[...] [('Spongebob Squarepants', 1), ('Patrick Star', 3), ('Eugene H. Krabs', 5)]
<...>

The RETURNING feature is not available when using the “bulk UPDATE by primary key” feature; the list of multiple parameter dictionaries necessarily makes use of DBAPI , which in its usual form does not typically support result rows.

Changed in version 2.0: Passing an Update construct to the method along with a list of parameter dictionaries and no WHERE criteria now invokes a “bulk update”, which makes use of the same functionality as the legacy Session.bulk_update_mappings() method. This is a behavior change compared to the 1.x series where the would only be supported with explicit WHERE criteria and inline VALUES.

Bulk UPDATE by Primary Key for Joined Table Inheritance

ORM bulk update has similar behavior to ORM bulk insert when using mappings with joined table inheritance; as described at , the bulk UPDATE operation will emit an UPDATE statement for each table represented in the mapping, for which the given parameters include values to be updated (non-affected tables are skipped).

Example:

>>> session.execute(
...     update(Manager),
...     [
...         {
...             "id": 1,
...             "name": "scheeks",
...             "manager_name": "Sandy Cheeks, President",
...         },
...         {
...             "id": 2,
...             "name": "eugene",
...             "manager_name": "Eugene H. Krabs, VP Marketing",
...         },
...     ],
... )
UPDATE employee SET name=? WHERE employee.id = ?
[...] [('scheeks', 1), ('eugene', 2)]
[...] [('Sandy Cheeks, President', 1), ('Eugene H. Krabs, VP Marketing', 2)]
<...>

Legacy Session Bulk UPDATE Methods

As discussed at , the Session.bulk_update_mappings() method of is the legacy form of bulk update, which the ORM makes use of internally when interpreting a update() statement with primary key parameters given; however, when using the legacy version, features such as support for session-synchronization are not included.

The example below:

session.bulk_update_mappings(
    User,
    [
        {"id": 1, "name": "scheeks", "manager_name": "Sandy Cheeks, President"},
        {"id": 2, "name": "eugene", "manager_name": "Eugene H. Krabs, VP Marketing"},
    ],
)

Is expressed using the new API as:

from sqlalchemy import update
session.execute(
    [
        {"id": 1, "name": "scheeks", "manager_name": "Sandy Cheeks, President"},
        {"id": 2, "name": "eugene", "manager_name": "Eugene H. Krabs, VP Marketing"},
    ],
)

See also

The and Delete constructs, when constructed with custom WHERE criteria (that is, using the and Delete.where() methods), may be invoked in an ORM context by passing them to , without using the Session.execute.params parameter. For , the values to be updated should be passed using Update.values().

This mode of use differs from the feature described previously at in that the ORM uses the given WHERE clause as is, rather than fixing the WHERE clause to be by primary key. This means that the single UPDATE or DELETE statement can affect many rows at once.

As an example, below an UPDATE is emitted that affects the “fullname” field of multiple rows

>>> from sqlalchemy import update
>>> stmt = (
...     update(User)
...     .where(User.name.in_(["squidward", "sandy"]))
...     .values(fullname="Name starts with S")
... )
>>> session.execute(stmt)
UPDATE user_account SET fullname=? WHERE user_account.name IN (?, ?)
[...] ('Name starts with S', 'squidward', 'sandy')
<...>

For a DELETE, an example of deleting rows based on criteria:

When making use of or delete() in conjunction with ORM-enabled execution using , additional ORM-specific functionality is present which will synchronize the state being changed by the statement with that of the objects that are currently present within the identity map of the . By “synchronize” we mean that UPDATEd attributes will be refreshed with the new value, or at the very least expired so that they will re-populate with their new value on next access, and DELETEd objects will be moved into the state.

This synchronization is controllable as the “synchronization strategy”, which is passed as an string ORM execution option, typically by using the Session.execute.execution_options dictionary:

>>> from sqlalchemy import update
>>> stmt = (
...     update(User).where(User.name == "squidward").values(fullname="Squidward Tentacles")
... )
>>> session.execute(stmt, execution_options={"synchronize_session": False})
UPDATE user_account SET fullname=? WHERE user_account.name = ?
[...] ('Squidward Tentacles', 'squidward')
<...>

The execution option may also be bundled with the statement itself using the method:

>>> from sqlalchemy import update
>>> stmt = (
...     update(User)
...     .where(User.name == "squidward")
...     .values(fullname="Squidward Tentacles")
...     .execution_options(synchronize_session=False)
... )
>>> session.execute(stmt)
UPDATE user_account SET fullname=? WHERE user_account.name = ?
[...] ('Squidward Tentacles', 'squidward')
<...>

The following values for synchronize_session are supported:

'auto' - this is the default. The 'fetch' strategy will be used on backends that support RETURNING, which includes all SQLAlchemy-native drivers except for MySQL. If RETURNING is not supported, the 'evaluate' strategy will be used instead.
'fetch' - Retrieves the primary key identity of affected rows by either performing a SELECT before the UPDATE or DELETE, or by using RETURNING if the database supports it, so that in-memory objects which are affected by the operation can be refreshed with new values (updates) or expunged from the Session (deletes). This synchronization strategy may be used even if the given or delete() construct explicitly specifies entities or columns using .

Changed in version 2.0: Explicit UpdateBase.returning() may be combined with the 'fetch' synchronization strategy when using ORM-enabled UPDATE and DELETE with WHERE criteria. The actual statement will contain the union of columns between that which the 'fetch' strategy requires and those which were requested.
'evaluate' - This indicates to evaluate the WHERE criteria given in the UPDATE or DELETE statement in Python, to locate matching objects within the . This approach does not add any SQL round trips to the operation, and in the absence of RETURNING support, may be more efficient. For UPDATE or DELETE statements with complex criteria, the 'evaluate' strategy may not be able to evaluate the expression in Python and will raise an error. If this occurs, use the 'fetch' strategy for the operation instead.

Tip

If a SQL expression makes use of custom operators using the Operators.op() or feature, the Operators.op.python_impl parameter may be used to indicate a Python function that will be used by the "evaluate" synchronization strategy.

New in version 2.0.

Warning

The "evaluate" strategy should be avoided if an UPDATE operation is to run on a that has many objects which have been expired, because it will necessarily need to refresh objects in order to test them against the given WHERE criteria, which will emit a SELECT for each one. In this case, and particularly if the backend supports RETURNING, the "fetch" strategy should be preferred.
False - don’t synchronize the session. This option may be useful for backends that don’t support RETURNING where the "evaluate" strategy is not able to be used. In this case, the state of objects in the Session is unchanged and will not automatically correspond to the UPDATE or DELETE statement that was emitted, if such objects that would normally correspond to the rows matched are present.

Using RETURNING with UPDATE/DELETE and Custom WHERE Criteria

The UpdateBase.returning() method is fully compatible with ORM-enabled UPDATE and DELETE with WHERE criteria. Full ORM objects and/or columns may be indicated for RETURNING:

>>> from sqlalchemy import update
>>> stmt = (
...     update(User)
...     .where(User.name == "squidward")
...     .values(fullname="Squidward Tentacles")
...     .returning(User)
... )
>>> result = session.scalars(stmt)
UPDATE user_account SET fullname=? WHERE user_account.name = ?
RETURNING id, name, fullname, species
[...] ('Squidward Tentacles', 'squidward')
>>> print(result.all())
[User(name='squidward', fullname='Squidward Tentacles')]

The support for RETURNING is also compatible with the fetch synchronization strategy, which also uses RETURNING. The ORM will organize the columns in RETURNING appropriately so that the synchronization proceeds as well as that the returned will contain the requested entities and SQL columns in their requested order.

New in version 2.0: UpdateBase.returning() may be used for ORM enabled UPDATE and DELETE while still retaining full compatibility with the fetch synchronization strategy.

UPDATE/DELETE with Custom WHERE Criteria for Joined Table Inheritance

The UPDATE/DELETE with WHERE criteria feature, unlike the ORM Bulk UPDATE by Primary Key, only emits a single UPDATE or DELETE statement per call to . This means that when running an update() or statement against a multi-table mapping, such as a subclass in a joined-table inheritance mapping, the statement must conform to the backend’s current capabilities, which may include that the backend does not support an UPDATE or DELETE statement that refers to multiple tables, or may have only limited support for this. This means that for mappings such as joined inheritance subclasses, the ORM version of the UPDATE/DELETE with WHERE criteria feature can only be used to a limited extent or not at all, depending on specifics.

The most straightforward way to emit a multi-row UPDATE statement for a joined-table subclass is to refer to the sub-table alone. This means the Update() construct should only refer to attributes that are local to the subclass table, as in the example below:

>>> stmt = (
...     update(Manager)
...     .where(Manager.id == 1)
...     .values(manager_name="Sandy Cheeks, President")
... )
>>> session.execute(stmt)
UPDATE manager SET manager_name=? WHERE manager.id = ?
[...] ('Sandy Cheeks, President', 1)
<...>

With the above form, a rudimentary way to refer to the base table in order to locate rows which will work on any SQL backend is so use a subquery:

>>> stmt = (
...     update(Manager)
...     .where(
...         Manager.id
...         == select(Employee.id).where(Employee.name == "sandy").scalar_subquery()
...     )
...     .values(manager_name="Sandy Cheeks, President")
... )
>>> session.execute(stmt)
UPDATE manager SET manager_name=? WHERE manager.id = (SELECT employee.id
FROM employee
WHERE employee.name = ?) RETURNING id
[...] ('Sandy Cheeks, President', 'sandy')
<...>

For backends that support UPDATE…FROM, the subquery may be stated instead as additional plain WHERE criteria, however the criteria between the two tables must be stated explicitly in some way:

>>> stmt = (
...     update(Manager)
...     .where(Manager.id == Employee.id, Employee.name == "sandy")
...     .values(manager_name="Sandy Cheeks, President")
... )
>>> session.execute(stmt)
UPDATE manager SET manager_name=? FROM employee
WHERE manager.id = employee.id AND employee.name = ?
[...] ('Sandy Cheeks, President', 'sandy')
<...>

For a DELETE, it’s expected that rows in both the base table and the sub-table would be DELETEd at the same time. To DELETE many rows of joined inheritance objects without using cascading foreign keys, emit DELETE for each table individually:

>>> from sqlalchemy import delete
>>> session.execute(delete(Manager).where(Manager.id == 1))
DELETE FROM manager WHERE manager.id = ?
[...] (1,)
<...>
>>> session.execute(delete(Employee).where(Employee.id == 1))
DELETE FROM employee WHERE employee.id = ?
[...] (1,)
<...>

Overall, normal processes should be preferred for updating and deleting rows for joined inheritance and other multi-table mappings, unless there is a performance rationale for using custom WHERE criteria.

Legacy Query Methods

The ORM enabled UPDATE/DELETE with WHERE feature was originally part of the now-legacy object, in the Query.update() and methods. These methods remain available and provide a subset of the same functionality as that described at ORM UPDATE and DELETE with Custom WHERE Criteria. The primary difference is that the legacy methods don’t provide for explicit RETURNING support.

See also

Query.delete()

ORM Querying Guide