support Postgresql / MySQL DELETE...USING [LEFT OUTER] JOIN

[tgross35]

Describe the use case

It doesn't currently seem possible to add a .join() to a delete() object for multi-table deletes. Currently, the following is possible (implicit inner join):

q=delete(TableA).filter(
            TableA.something == 'something',
            TableA.b_id == TableB.id,
            TableB.something == 'abcd',
        ).execution_options(synchronize_session="fetch")
q.stringify_dialect = 'mysql'
str(q)

DELETE FROM table_a USING table_a, table_b
WHERE 'something' = table_a.something
AND table_a.table_b_id = table_b.id
AND table_b.something = 'abcd'

But it would be convenient to be able to specify the join condition using an ORM relationship, and use the other types of joins possible (left).

Databases / Backends / Drivers targeted

This at least works in mysql and mariadb, probably some others (unsure which)

Good examples are here: https://www.mysqltutorial.org/mysql-delete-join/

Example Use

Rough example (untested)

q=delete(TableA).join(TableA.b_relationship)filter(
            TableA.something == 'something',
            TableB.something == 'abcd',
        ).execution_options(synchronize_session="fetch")

DELETE TableA 
FROM TableA
JOIN TableB ON TableA .b_id = TableB.id
WHERE
    TableA.something = 'something'
AND
    TableB.something = 'abcd';

Additional context

It is also possible to join on UPDATE - I am unsure what the current state of support for that is. See here: https://www.mysqltutorial.org/mysql-update-join/

Relevant current doc location: https://docs.sqlalchemy.org/en/14/core/tutorial.html#multiple-table-deletes

[zzzeek]

we support MySQLs join() for UPDATE, for DELETE it does not seem we have specific tests.

this would be a Core use case, not ORM. the way it works is to pass join() directly to delete(). the relationship part you can get using orm.join().

from sqlalchemy.orm import join

stmt = delete(join(ModelA, ModelB, ModelA.bs)) ...

right now it comes out like

DELETE FROM a INNER JOIN b ON a.id = b.a_id USING a INNER JOIN b ON a.id = b.a_id, b

also it would be nice if both the update() and the delete() cases had some docs at https://docs.sqlalchemy.org/en/14/dialects/mysql.html#mysql-mariadb-sql-extensions

[zzzeek]

can that JOIN be in the USING clause ?

[CaselIT]

Pg supports this also, but only on the using https://www.postgresql.org/docs/current/sql-delete.html

[tgross35]

Looks like the join condition can be after USING, this works for me

DELETE FROM T1 USING T1 LEFT JOIN T2
ON T1.id = T2.t1_id
WHERE T2.value = 'deleteme';

Per the docs, either of these are allowable and I think equivalent. The join just goes in table_references

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

Edit: for completeness, the following works too

DELETE T1 FROM T1 LEFT JOIN T2
ON T1.id = T2.t1_id
WHERE T2.value = 'deleteme';

Kind of annoying how USING makes FROM change its purpose completely

[CaselIT]

Pg supports this also, but only on the using https://www.postgresql.org/docs/current/sql-delete.html

But on pg side it seems that it can be used only to filter the rows. It does not lalow deleting from multiple tables, so probably not the same functionality

[tgross35]

from sqlalchemy.orm import join

stmt = delete(join(ModelA, ModelB, ModelA.bs)) ...

Does this distinguish between deleting from only ModelA and deleting from both ModelA and ModelB? Or am I missing context

[zzzeek]

well for DELETE it's whatever the database does I guess? we just print a string

[tgross35]

Only from ModelA as-is (though the rendered sql syntax you sent is broken of course), I guess I was moreso wondering if there would be a way to specify a delete from both tables. Assuming that the python syntax is what the goal is, wasn't sure if the goal is to make that work or if that's just an existing workaround until better syntax could be added.

[iTrooz]

stmt = delete(TableA).join(TableB).filter(
    TableA.something == 'something',
    TableB.something == 'abcd'
)

Hey, is the following syntax being worked on ? If not, I would like to pick it up, would you accept a PR ?

[zzzeek]

yes we accept PRs, in this case it has to be pretty complete with good compile and round trip tests etc. see test/sql/test_delete.py for where to go with this.

[zzzeek]

it would be nice if delete() had a using() construct directly, that accepts a join(). delete().join() itself would get pretty involved with all the ORM use cases