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

1 files changed, 31 insertions, 3 deletions

diff --git a/lib/sqlalchemy/sql/dml.py b/lib/sqlalchemy/sql/dml.py
index 958e9bfb1..6c8c550b4 100644
--- a/lib/sqlalchemy/sql/dml.py
+++ b/lib/sqlalchemy/sql/dml.py
@@ -656,7 +656,7 @@ class Update(ValuesBase):
                     )
 
          .. versionchanged:: 0.7.4
-             The WHERE clause can refer to multiple tables.
+             The WHERE clause of UPDATE can refer to multiple tables.
 
         :param values:
           Optional dictionary which specifies the ``SET`` conditions of the
@@ -768,8 +768,6 @@ class Update(ValuesBase):
 
     @property
     def _extra_froms(self):
-        # TODO: this could be made memoized
-        # if the memoization is reset on each generative call.
         froms = []
         seen = {self.table}
 
@@ -811,6 +809,23 @@ class Delete(UpdateBase):
           condition of the ``DELETE`` statement. Note that the
           :meth:`~Delete.where()` generative method may be used instead.
 
+         The WHERE clause can refer to multiple tables.
+         For databases which support this, a ``DELETE..USING`` or similar
+         clause will be generated.  The statement
+         will fail on databases that don't have support for multi-table
+         delete statements.  A SQL-standard method of referring to
+         additional tables in the WHERE clause is to use a correlated
+         subquery::
+
+            users.delete().where(
+                    users.c.name==select([addresses.c.email_address]).\
+                                where(addresses.c.user_id==users.c.id).\
+                                as_scalar()
+                    )
+
+         .. versionchanged:: 1.2.0
+             The WHERE clause of DELETE can refer to multiple tables.
+
         .. seealso::
 
             :ref:`deletes` - SQL Expression Tutorial
@@ -846,6 +861,19 @@ class Delete(UpdateBase):
         else:
             self._whereclause = _literal_as_text(whereclause)
 
+    @property
+    def _extra_froms(self):
+        froms = []
+        seen = {self.table}
+
+        if self._whereclause is not None:
+            for item in _from_objects(self._whereclause):
+                if not seen.intersection(item._cloned_set):
+                    froms.append(item)
+                seen.update(item._cloned_set)
+
+        return froms
+
     def _copy_internals(self, clone=_clone, **kw):
         # TODO: coverage
         self._whereclause = clone(self._whereclause, **kw)