support mysql DELETE FROM ... JOIN / Postgresql DELETE..USING ?

[sqlalchemy-bot]

Issue created by jek (jek)

---

delete(table-or-join) should be acceptable with the mysql dialect and produce the expected DELETE FROM basetable JOIN etc. currently:

>>> metadata.bind = 'mysql:///test'
>>> print delete(parents_table)
DELETE FROM parents
>>> print delete(parents_table.join(children_table))
Traceback (most recent call last):
...
AttributeError: 'Join' object has no attribute 'name'
>>> print delete(parents_table.join(children_table).alias('x'))
DELETE FROM x

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

---

PG supports the same thing via USING: http://www.postgresql.org/docs/8.3/interactive/sql-delete.html . Though I doubt its widely used in the PG community.

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

---

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

---

mysql has the USING keyword as well: http://dev.mysql.com/doc/refman/5.0/en/delete.html perhaps these can be done the same way

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

---

mysql seems to support it as simple "DELETE USING" as well http://dev.mysql.com/doc/refman/5.7/en/delete.html. if both syntaxes are identical here that would help a lot.

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

---

Allow delete where clause to refer multiple tables.

Implemented "DELETE..FROM" syntax for Postgresql, MySQL, MS SQL Server (as well as within the unsupported Sybase dialect) in a manner similar to how "UPDATE..FROM" works. A DELETE statement that refers to more than one table will switch into "multi-table" mode and render the appropriate "USING" or multi-table "FROM" clause as understood by the database. Pull request courtesy Pieter Mulder.

For SQL syntaxes see:

Postgresql: https://www.postgresql.org/docs/current/static/sql-delete.html MySQL: https://dev.mysql.com/doc/refman/5.7/en/delete.html#multiple-table_syntax MSSQL: https://docs.microsoft.com/en-us/sql/t-sql/statements/delete-transact-sql Sybase: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00801.1510/html/iqrefso/X315721.htm

Co-authored by: Mike Bayer mike_mp@zzzcomputing.com Change-Id: I6dfd57b49e44a095d076dc493cd2360bb5d920d3 Pull-request: https://github.com/zzzeek/sqlalchemy/pull/392 Fixes: #959

[sqlalchemy-bot]

Changes by Michael Bayer (zzzeek): set milestone to "blue sky"

[sqlalchemy-bot]

Changes by Michael Bayer (zzzeek): changed content from "delete(table-or-join) should be acceptable with the mysql dialect and produce the expected DELETE FROM basetable JOIN etc. currently:

>>> metadata.bind = 'mysql:///test'
>>> print delete(parents_table)
DELETE FROM parents
>>> print de" to "delete(table-or-join) should be acceptable with the mysql dialect and produce the expected `DELETE FROM basetable JOIN etc`.  currently:

>>> metadata.bind = 'mysql:///test'
>>> print delete(parents_table)
DELETE FROM parents
>>> pri"; removed "jek" assignee; removed "557058:43be0911-3b87-4a7e-b7e0-3acf3ea5bb40" assignee_account_id; changed component from "mysql" to "sql"; changed title from "support mysql DELETE FROM ... JOIN" to "support mysql DELETE FROM ... JOIN / Postgresql DELETE..USING ?"

[sqlalchemy-bot]

Changes by Michael Bayer (zzzeek): changed content from "delete(table-or-join) should be acceptable with the mysql dialect and produce the expected DELETE FROM basetable JOIN etc. currently:

>>> metadata.bind = 'mysql:///test'
>>> print delete(parents_table)
DELETE FROM parents
>>> pri" to "delete(table-or-join) should be acceptable with the mysql dialect and produce the expected `DELETE FROM basetable JOIN etc`.  currently:

>>> metadata.bind = 'mysql:///test'
>>> print delete(parents_table)
DELETE FROM parents
>>> pri"; changed milestone from "blue sky" to "1.2"

[sqlalchemy-bot]

Changes by Michael Bayer (zzzeek): changed state from "new" to "resolved"