Skip to content

Commit fb3b47d

Browse files
committed
some clarifications to ORM enabled UPDATE/DELETE for joined inh
references: #13569 Change-Id: I62eba3ab443951d4c8a18687649a1158a7f2fc1a
1 parent 9a0adaf commit fb3b47d

1 file changed

Lines changed: 25 additions & 8 deletions

File tree

doc/build/orm/queryguide/dml.rst

Lines changed: 25 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1030,9 +1030,10 @@ that matches multiple rows at once without complexity.
10301030
is expired, which normally occurs upon :meth:`.Session.commit` or can be
10311031
forced by using :meth:`.Session.expire_all`.
10321032

1033-
* ORM-enabled UPDATEs and DELETEs do not handle joined table inheritance
1034-
automatically. See the section :ref:`orm_queryguide_update_delete_joined_inh`
1035-
for notes on how to work with joined-inheritance mappings.
1033+
* ORM-enabled UPDATEs and DELETEs **do not handle joined table inheritance
1034+
automatically**. See the section
1035+
:ref:`orm_queryguide_update_delete_joined_inh` for notes on how to work
1036+
with joined-inheritance mappings.
10361037

10371038
* The WHERE criteria needed in order to limit the polymorphic identity to
10381039
specific subclasses for single-table-inheritance mappings **is included
@@ -1207,6 +1208,21 @@ means that for mappings such as joined inheritance subclasses, the ORM version
12071208
of the UPDATE/DELETE with WHERE criteria feature can only be used to a limited
12081209
extent or not at all, depending on specifics.
12091210

1211+
.. warning:: as mentioned previously, ORM enabled UPDATE and DELETE **do not
1212+
automatically apply joining criteria between parent/child tables when using
1213+
multi-table forms of these statements**.
1214+
This
1215+
criteria must be assembled manually, as in the examples below.
1216+
Additionally, for deletes, rows must always be deleted from both parent
1217+
and child tables at the same time, which means that **multi-table DELETE
1218+
forms are not generally useful for deleting joined-table inheritance
1219+
mappings**, since these forms do not actually remove rows from multiple
1220+
tables.
1221+
1222+
When using multi-table forms, always test such queries on a test database, and always look out for the
1223+
:ref:`cartesian product warning <change_4737>` when testing, which would
1224+
indicate that more rows are being matched than are probably intended.
1225+
12101226
The most straightforward way to emit a multi-row UPDATE statement
12111227
for a joined-table subclass is to refer to the sub-table alone.
12121228
This means the :func:`_dml.Update` construct should only refer to attributes
@@ -1256,11 +1272,12 @@ tables must be stated explicitly in some way::
12561272
[...] ('Sandy Cheeks, President', 'sandy')
12571273
{stop}<...>
12581274

1259-
1260-
For a DELETE, it's expected that rows in both the base table and the sub-table
1261-
would be DELETEd at the same time. To DELETE many rows of joined inheritance
1262-
objects **without** using cascading foreign keys, emit DELETE for each
1263-
table individually::
1275+
For DELETEs, while many backends such as PostgreSQL, MySQL/MariaDB, and
1276+
SQL Server support multi-table forms of DELETE, the statements only
1277+
delete rows from the primary table of the statement. Therefore, to DELETE
1278+
many rows of a joined inheritance mapping, either foreign key constraints
1279+
that include ``ON DELETE CASCADE`` should be configured, or
1280+
**an individual DELETE statement per table** must be emitted, as below::
12641281

12651282
>>> from sqlalchemy import delete
12661283
>>> session.execute(delete(Manager).where(Manager.id == 1))

0 commit comments

Comments
 (0)