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

20 files changed, 526 insertions, 18 deletions

diff --git a/doc/build/changelog/migration_08.rst b/doc/build/changelog/migration_08.rst
index 64ef46c26..6db942b39 100644
--- a/doc/build/changelog/migration_08.rst
+++ b/doc/build/changelog/migration_08.rst
@@ -526,6 +526,8 @@ the :class:`.Table` to which ``User`` is mapped.
 
 :ticket:`2245`
 
+.. _change_orm_2365:
+
 Query.update() supports UPDATE..FROM
 ------------------------------------
 
diff --git a/doc/build/changelog/migration_12.rst b/doc/build/changelog/migration_12.rst
index e78c44fd7..9c0218c3a 100644
--- a/doc/build/changelog/migration_12.rst
+++ b/doc/build/changelog/migration_12.rst
@@ -245,6 +245,31 @@ if not specified, the attribute defaults to ``None``::
 
 :ticket:`3058`
 
+.. _change_orm_959:
+
+ORM Support of multiple-table deletes
+-------------------------------------
+
+The ORM :meth:`.Query.delete` method supports multiple-table criteria
+for DELETE, as introduced in :ref:`change_959`.   The feature works
+in the same manner as multiple-table criteria for UPDATE, first
+introduced in 0.8 and described at :ref:`change_orm_2365`.
+
+Below, we emit a DELETE against ``SomeEntity``, adding
+a FROM clause (or equivalent, depending on backend)
+against ``SomeOtherEntity``::
+
+    query(SomeEntity).\
+        filter(SomeEntity.id==SomeOtherEntity.id).\
+        filter(SomeOtherEntity.foo=='bar').\
+        delete()
+
+.. seealso::
+
+    :ref:`change_959`
+
+:ticket:`959`
+
 .. _change_3229:
 
 Support for bulk updates of hybrids, composites
@@ -776,6 +801,37 @@ Current backend support includes MySQL, Postgresql, and Oracle.
 
 :ticket:`1546`
 
+.. _change_959:
+
+Multiple-table criteria support for DELETE
+------------------------------------------
+
+The :class:`.Delete` construct now supports multiple-table criteria,
+implemented for those backends which support it, currently these are
+Postgresql, MySQL and Microsoft SQL Server (support is also added to the
+currently non-working Sybase dialect).   The feature works in the same
+was as that of multiple-table criteria for UPDATE, first introduced in
+the 0.7 and 0.8 series.
+
+Given a statement as::
+
+    stmt = users.delete().\
+            where(users.c.id == addresses.c.id).\
+            where(addresses.c.email_address.startswith('ed%'))
+    conn.execute(stmt)
+
+The resulting SQL from the above statement on a Postgresql backend
+would render as::
+
+    DELETE FROM users USING addresses
+    WHERE users.id = addresses.id
+    AND (addresses.email_address LIKE %(email_address_1)s || '%%')
+
+.. seealso::
+
+    :ref:`multi_table_deletes`
+
+:ticket:`959`
 
 .. _change_2694:
 
diff --git a/doc/build/changelog/unreleased_12/959.rst b/doc/build/changelog/unreleased_12/959.rst
new file mode 100644
index 000000000..bcb9cbcf7
--- /dev/null
+++ b/doc/build/changelog/unreleased_12/959.rst
@@ -0,0 +1,14 @@
+.. change::
+    :tags: enhancement, sql
+    :tickets: 959
+
+    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.
+
+    .. seealso::
+
+        :ref:`change_959`
\ No newline at end of file
diff --git a/doc/build/core/tutorial.rst b/doc/build/core/tutorial.rst
index b8a362daa..1069ae5c7 100644
--- a/doc/build/core/tutorial.rst
+++ b/doc/build/core/tutorial.rst
@@ -2055,10 +2055,11 @@ The tables are referenced explicitly in the SET clause::
             users.name=%s WHERE users.id = addresses.id
             AND addresses.email_address LIKE concat(%s, '%')
 
-SQLAlchemy doesn't do anything special when these constructs are used on
-a non-supporting database.  The ``UPDATE FROM`` syntax generates by default
-when multiple tables are present, and the statement will be rejected
-by the database if this syntax is not supported.
+When the construct is used on a non-supporting database, the compiler
+will raise ``NotImplementedError``.   For convenience, when a statement
+is printed as a string without specification of a dialect, the "string SQL"
+compiler will be invoked which provides a non-working SQL representation of the
+construct.
 
 .. _updates_order_parameters:
 
@@ -2129,6 +2130,37 @@ Finally, a delete.  This is accomplished easily enough using the
     COMMIT
     {stop}<sqlalchemy.engine.result.ResultProxy object at 0x...>
 
+.. _multi_table_deletes:
+
+Multiple Table Deletes
+----------------------
+
+.. versionadded:: 1.2
+
+The PostgreSQL, Microsoft SQL Server, and MySQL backends all support DELETE
+statements that refer to multiple tables within the WHERE criteria.   For PG
+and MySQL, this is the "DELETE USING" syntax, and for SQL Server, it's a
+"DELETE FROM" that refers to more than one table.  The SQLAlchemy
+:func:`.delete` construct supports both of these modes
+implicitly, by specifying multiple tables in the WHERE clause::
+
+    stmt = users.delete().\
+            where(users.c.id == addresses.c.id).\
+            where(addresses.c.email_address.startswith('ed%'))
+    conn.execute(stmt)
+
+On a Postgresql backend, the resulting SQL from the above statement would render as::
+
+    DELETE FROM users USING addresses
+    WHERE users.id = addresses.id
+    AND (addresses.email_address LIKE %(email_address_1)s || '%%')
+
+When the construct is used on a non-supporting database, the compiler
+will raise ``NotImplementedError``.   For convenience, when a statement
+is printed as a string without specification of a dialect, the "string SQL"
+compiler will be invoked which provides a non-working SQL representation of the
+construct.
+
 Matched Row Counts
 ------------------
 
diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 0c3688c9b..9f4e7a9c4 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -1510,6 +1510,28 @@ class MSSQLCompiler(compiler.SQLCompiler):
                                  fromhints=from_hints, **kw)
             for t in [from_table] + extra_froms)
 
+    def delete_table_clause(self, delete_stmt, from_table,
+                            extra_froms):
+        """If we have extra froms make sure we render any alias as hint."""
+        ashint = False
+        if extra_froms:
+            ashint = True
+        return from_table._compiler_dispatch(
+            self, asfrom=True, iscrud=True, ashint=ashint
+        )
+
+    def delete_extra_from_clause(self, delete_stmt, from_table,
+                                 extra_froms, from_hints, **kw):
+        """Render the DELETE .. FROM clause specific to MSSQL.
+
+        Yes, it has the FROM keyword twice.
+
+        """
+        return "FROM " + ', '.join(
+            t._compiler_dispatch(self, asfrom=True,
+                                 fromhints=from_hints, **kw)
+            for t in [from_table] + extra_froms)
+
 
 class MSSQLStrictCompiler(MSSQLCompiler):
 
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index bee62b76f..09f8a2a0e 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -1102,6 +1102,24 @@ class MySQLCompiler(compiler.SQLCompiler):
                            extra_froms, from_hints, **kw):
         return None
 
+    def delete_table_clause(self, delete_stmt, from_table,
+                            extra_froms):
+        """If we have extra froms make sure we render any alias as hint."""
+        ashint = False
+        if extra_froms:
+            ashint = True
+        return from_table._compiler_dispatch(
+            self, asfrom=True, iscrud=True, ashint=ashint
+        )
+
+    def delete_extra_from_clause(self, delete_stmt, from_table,
+                           extra_froms, from_hints, **kw):
+        """Render the DELETE .. USING clause specific to MySQL."""
+        return "USING " + ', '.join(
+            t._compiler_dispatch(self, asfrom=True,
+                                 fromhints=from_hints, **kw)
+            for t in [from_table] + extra_froms)
+
 
 class MySQLDDLCompiler(compiler.DDLCompiler):
     def get_column_specification(self, column, **kw):
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 32de7861a..043efd6df 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -1647,6 +1647,23 @@ class PGCompiler(compiler.SQLCompiler):
 
         return 'ON CONFLICT %s DO UPDATE SET %s' % (target_text, action_text)
 
+    def update_from_clause(self, update_stmt,
+                           from_table, extra_froms,
+                           from_hints,
+                           **kw):
+        return "FROM " + ', '.join(
+            t._compiler_dispatch(self, asfrom=True,
+                                 fromhints=from_hints, **kw)
+            for t in extra_froms)
+
+    def delete_extra_from_clause(self, delete_stmt, from_table,
+                           extra_froms, from_hints, **kw):
+        """Render the DELETE .. USING clause specific to PostgresSQL."""
+        return "USING " + ', '.join(
+            t._compiler_dispatch(self, asfrom=True,
+                                 fromhints=from_hints, **kw)
+            for t in extra_froms)
+
 
 class PGDDLCompiler(compiler.DDLCompiler):
 
diff --git a/lib/sqlalchemy/dialects/sybase/base.py b/lib/sqlalchemy/dialects/sybase/base.py
index 5d2f0f70c..0d7d03e78 100644
--- a/lib/sqlalchemy/dialects/sybase/base.py
+++ b/lib/sqlalchemy/dialects/sybase/base.py
@@ -369,6 +369,24 @@ class SybaseSQLCompiler(compiler.SQLCompiler):
         else:
             return ""
 
+    def delete_table_clause(self, delete_stmt, from_table,
+                            extra_froms):
+        """If we have extra froms make sure we render any alias as hint."""
+        ashint = False
+        if extra_froms:
+            ashint = True
+        return from_table._compiler_dispatch(
+            self, asfrom=True, iscrud=True, ashint=ashint
+        )
+
+    def delete_extra_from_clause(self, delete_stmt, from_table,
+                                 extra_froms, from_hints, **kw):
+        """Render the DELETE .. FROM clause specific to Sybase."""
+        return "FROM " + ', '.join(
+            t._compiler_dispatch(self, asfrom=True,
+                                 fromhints=from_hints, **kw)
+            for t in [from_table] + extra_froms)
+
 
 class SybaseDDLCompiler(compiler.DDLCompiler):
     def get_column_specification(self, column, **kwargs):
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index b0f0807d6..9ed75ca06 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -2146,10 +2146,9 @@ class SQLCompiler(Compiled):
         MySQL and MSSQL override this.
 
         """
-        return "FROM " + ', '.join(
-            t._compiler_dispatch(self, asfrom=True,
-                                 fromhints=from_hints, **kw)
-            for t in extra_froms)
+        raise NotImplementedError(
+            "This backend does not support multiple-table "
+            "criteria within UPDATE")
 
     def visit_update(self, update_stmt, asfrom=False, **kw):
         toplevel = not self.stack
@@ -2232,6 +2231,25 @@ class SQLCompiler(Compiled):
     def _key_getters_for_crud_column(self):
         return crud._key_getters_for_crud_column(self, self.statement)
 
+    def delete_extra_from_clause(self, update_stmt,
+                                 from_table, extra_froms,
+                                 from_hints, **kw):
+        """Provide a hook to override the generation of an
+        DELETE..FROM clause.
+
+        This can be used to implement DELETE..USING for example.
+
+        MySQL and MSSQL override this.
+
+        """
+        raise NotImplementedError(
+            "This backend does not support multiple-table "
+            "criteria within DELETE")
+
+    def delete_table_clause(self, delete_stmt, from_table,
+                            extra_froms):
+        return from_table._compiler_dispatch(self, asfrom=True, iscrud=True)
+
     def visit_delete(self, delete_stmt, asfrom=False, **kw):
         toplevel = not self.stack
 
@@ -2241,6 +2259,8 @@ class SQLCompiler(Compiled):
 
         crud._setup_crud_params(self, delete_stmt, crud.ISDELETE, **kw)
 
+        extra_froms = delete_stmt._extra_froms
+
         text = "DELETE "
 
         if delete_stmt._prefixes:
@@ -2248,12 +2268,14 @@ class SQLCompiler(Compiled):
                                             delete_stmt._prefixes, **kw)
 
         text += "FROM "
-        table_text = delete_stmt.table._compiler_dispatch(
-            self, asfrom=True, iscrud=True)
+        table_text = self.delete_table_clause(delete_stmt, delete_stmt.table,
+                                              extra_froms)
 
         if delete_stmt._hints:
             dialect_hints, table_text = self._setup_crud_hints(
                 delete_stmt, table_text)
+        else:
+            dialect_hints = None
 
         text += table_text
 
@@ -2262,6 +2284,15 @@ class SQLCompiler(Compiled):
                 text += " " + self.returning_clause(
                     delete_stmt, delete_stmt._returning)
 
+        if extra_froms:
+            extra_from_text = self.delete_extra_from_clause(
+                delete_stmt,
+                delete_stmt.table,
+                extra_froms,
+                dialect_hints, **kw)
+            if extra_from_text:
+                text += " " + extra_from_text
+
         if delete_stmt._whereclause is not None:
             t = delete_stmt._whereclause._compiler_dispatch(self, **kw)
             if t:
@@ -2324,6 +2355,24 @@ class StrSQLCompiler(SQLCompiler):
 
         return 'RETURNING ' + ', '.join(columns)
 
+    def update_from_clause(self, update_stmt,
+                           from_table, extra_froms,
+                           from_hints,
+                           **kw):
+        return "FROM " + ', '.join(
+            t._compiler_dispatch(self, asfrom=True,
+                                 fromhints=from_hints, **kw)
+            for t in extra_froms)
+
+    def delete_extra_from_clause(self, update_stmt,
+                           from_table, extra_froms,
+                           from_hints,
+                           **kw):
+        return ', ' + ', '.join(
+            t._compiler_dispatch(self, asfrom=True,
+                                 fromhints=from_hints, **kw)
+            for t in extra_froms)
+
 
 class DDLCompiler(Compiled):
 
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)
diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py
index a66b091aa..0b89a4506 100644
--- a/lib/sqlalchemy/testing/requirements.py
+++ b/lib/sqlalchemy/testing/requirements.py
@@ -693,6 +693,11 @@ class SuiteRequirements(Requirements):
         return exclusions.closed()
 
     @property
+    def delete_from(self):
+        """Target must support DELETE FROM..FROM or DELETE..USING syntax"""
+        return exclusions.closed()
+
+    @property
     def update_where_target_in_subquery(self):
         """Target must support UPDATE where the same table is present in a
         subquery in the WHERE clause.
diff --git a/test/dialect/mssql/test_compiler.py b/test/dialect/mssql/test_compiler.py
index 1f4a4da4b..d62753b9d 100644
--- a/test/dialect/mssql/test_compiler.py
+++ b/test/dialect/mssql/test_compiler.py
@@ -139,6 +139,23 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             "sometable.somecolumn = :somecolumn_1"
         )
 
+    def test_delete_extra_froms(self):
+        t1 = table('t1', column('c1'))
+        t2 = table('t2', column('c1'))
+        q = sql.delete(t1).where(t1.c.c1 == t2.c.c1)
+        self.assert_compile(
+            q, "DELETE FROM t1 FROM t1, t2 WHERE t1.c1 = t2.c1"
+        )
+
+    def test_delete_extra_froms_alias(self):
+        a1 = table('t1', column('c1')).alias('a1')
+        t2 = table('t2', column('c1'))
+        q = sql.delete(a1).where(a1.c.c1 == t2.c.c1)
+        self.assert_compile(
+            q, "DELETE FROM a1 FROM t1 AS a1, t2 WHERE a1.c1 = t2.c1"
+        )
+        self.assert_compile(sql.delete(a1), "DELETE FROM t1 AS a1")
+
     def test_update_from_hint(self):
         t = table('sometable', column('somecolumn'))
         t2 = table('othertable', column('somecolumn'))
diff --git a/test/dialect/mysql/test_compiler.py b/test/dialect/mysql/test_compiler.py
index cebbfc896..fdf799539 100644
--- a/test/dialect/mysql/test_compiler.py
+++ b/test/dialect/mysql/test_compiler.py
@@ -232,6 +232,23 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             "SELECT mytable.myid, mytable.name, mytable.description "
             "FROM mytable WHERE mytable.myid = %s LOCK IN SHARE MODE")
 
+    def test_delete_extra_froms(self):
+        t1 = table('t1', column('c1'))
+        t2 = table('t2', column('c1'))
+        q = sql.delete(t1).where(t1.c.c1 == t2.c.c1)
+        self.assert_compile(
+            q, "DELETE FROM t1 USING t1, t2 WHERE t1.c1 = t2.c1"
+        )
+
+    def test_delete_extra_froms_alias(self):
+        a1 = table('t1', column('c1')).alias('a1')
+        t2 = table('t2', column('c1'))
+        q = sql.delete(a1).where(a1.c.c1 == t2.c.c1)
+        self.assert_compile(
+            q, "DELETE FROM a1 USING t1 AS a1, t2 WHERE a1.c1 = t2.c1"
+        )
+        self.assert_compile(sql.delete(a1), "DELETE FROM t1 AS a1")
+
 
 class SQLTest(fixtures.TestBase, AssertsCompiledSQL):
 
diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py
index 18940ed5f..db142a657 100644
--- a/test/dialect/postgresql/test_compiler.py
+++ b/test/dialect/postgresql/test_compiler.py
@@ -1091,6 +1091,22 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             "FROM table1 AS foo"
         )
 
+    def test_delete_extra_froms(self):
+        t1 = table('t1', column('c1'))
+        t2 = table('t2', column('c1'))
+        q = delete(t1).where(t1.c.c1 == t2.c.c1)
+        self.assert_compile(
+            q, "DELETE FROM t1 USING t2 WHERE t1.c1 = t2.c1"
+        )
+
+    def test_delete_extra_froms_alias(self):
+        a1 = table('t1', column('c1')).alias('a1')
+        t2 = table('t2', column('c1'))
+        q = delete(a1).where(a1.c.c1 == t2.c.c1)
+        self.assert_compile(
+            q, "DELETE FROM t1 AS a1 USING t2 WHERE a1.c1 = t2.c1"
+        )
+
 
 class InsertOnConflictTest(fixtures.TestBase, AssertsCompiledSQL):
     __dialect__ = postgresql.dialect()
diff --git a/test/dialect/test_sybase.py b/test/dialect/test_sybase.py
index fee72a5b7..602747106 100644
--- a/test/dialect/test_sybase.py
+++ b/test/dialect/test_sybase.py
@@ -32,3 +32,20 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             "Sybase ASE does not support OFFSET",
             stmt.compile, dialect=self.__dialect__
         )
+
+    def test_delete_extra_froms(self):
+        t1 = sql.table('t1', sql.column('c1'))
+        t2 = sql.table('t2', sql.column('c1'))
+        q = sql.delete(t1).where(t1.c.c1 == t2.c.c1)
+        self.assert_compile(
+            q, "DELETE FROM t1 FROM t1, t2 WHERE t1.c1 = t2.c1"
+        )
+
+    def test_delete_extra_froms_alias(self):
+        a1 = sql.table('t1', sql.column('c1')).alias('a1')
+        t2 = sql.table('t2', sql.column('c1'))
+        q = sql.delete(a1).where(a1.c.c1 == t2.c.c1)
+        self.assert_compile(
+            q, "DELETE FROM a1 FROM t1 AS a1, t2 WHERE a1.c1 = t2.c1"
+        )
+        self.assert_compile(sql.delete(a1), "DELETE FROM t1 AS a1")
diff --git a/test/orm/test_update_delete.py b/test/orm/test_update_delete.py
index e387ad9e6..98fcc4f00 100644
--- a/test/orm/test_update_delete.py
+++ b/test/orm/test_update_delete.py
@@ -726,6 +726,7 @@ class UpdateDeleteIgnoresLoadersTest(fixtures.MappedTest):
 
 
 class UpdateDeleteFromTest(fixtures.MappedTest):
+    __backend__ = True
 
     @classmethod
     def define_tables(cls, metadata):
@@ -802,6 +803,25 @@ class UpdateDeleteFromTest(fixtures.MappedTest):
                 (5, True), (6, None)])
         )
 
+    @testing.requires.delete_from
+    def test_delete_from_joined_subq_test(self):
+        Document = self.classes.Document
+        s = Session()
+
+        subq = s.query(func.max(Document.title).label('title')).\
+            group_by(Document.user_id).subquery()
+
+        s.query(Document).filter(Document.title == subq.c.title).\
+            delete(synchronize_session=False)
+
+        eq_(
+            set(s.query(Document.id, Document.flag)),
+            set([
+                (2, None),
+                (3, None),
+                (6, None)])
+        )
+
     def test_no_eval_against_multi_table_criteria(self):
         User = self.classes.User
         Document = self.classes.Document
@@ -1016,6 +1036,19 @@ class InheritTest(fixtures.DeclarativeMappedTest):
             set([('e1', 'e1', ), ('e2', 'e5')])
         )
 
+    @testing.requires.delete_from
+    def test_delete_from(self):
+        Engineer = self.classes.Engineer
+        Person = self.classes.Person
+        s = Session(testing.db)
+        s.query(Engineer).filter(Engineer.id == Person.id).\
+            filter(Person.name == 'e2').delete()
+
+        eq_(
+            set(s.query(Person.name, Engineer.engineer_name)),
+            set([('e1', 'e1', )])
+        )
+
     @testing.only_on('mysql', 'Multi table update')
     def test_update_from_multitable(self):
         Engineer = self.classes.Engineer
diff --git a/test/requirements.py b/test/requirements.py
index 39a78dfa5..be85c1c0d 100644
--- a/test/requirements.py
+++ b/test/requirements.py
@@ -325,6 +325,12 @@ class DefaultRequirements(SuiteRequirements):
                        "Backend does not support UPDATE..FROM")
 
     @property
+    def delete_from(self):
+        """Target must support DELETE FROM..FROM or DELETE..USING syntax"""
+        return only_on(['postgresql', 'mssql', 'mysql', 'sybase'],
+                       "Backend does not support UPDATE..FROM")
+
+    @property
     def update_where_target_in_subquery(self):
         """Target must support UPDATE where the same table is present in a
         subquery in the WHERE clause.
diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index 053619f46..988230ac5 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -2926,7 +2926,7 @@ class ExecutionOptionsTest(fixtures.TestBase):
 
 
 class CRUDTest(fixtures.TestBase, AssertsCompiledSQL):
-    __dialect__ = 'default'
+    __dialect__ = 'default_enhanced'
 
     def test_insert_literal_binds(self):
         stmt = table1.insert().values(myid=3, name='jack')
diff --git a/test/sql/test_delete.py b/test/sql/test_delete.py
index 904dcee3f..7d18db9c9 100644
--- a/test/sql/test_delete.py
+++ b/test/sql/test_delete.py
@@ -1,9 +1,11 @@
 #! coding:utf-8
 
-from sqlalchemy import Column, Integer, String, Table, delete, select, and_, \
+from sqlalchemy import Integer, String, ForeignKey, delete, select, and_, \
     or_
 from sqlalchemy.dialects import mysql
-from sqlalchemy.testing import AssertsCompiledSQL, fixtures
+from sqlalchemy import testing
+from sqlalchemy.testing import AssertsCompiledSQL, fixtures, eq_
+from sqlalchemy.testing.schema import Table, Column
 
 
 class _DeleteTestBase(object):
@@ -99,3 +101,142 @@ class DeleteTest(_DeleteTestBase, fixtures.TablesTest, AssertsCompiledSQL):
                             'FROM myothertable '
                             'WHERE myothertable.otherid = mytable.myid'
                             ')')
+
+
+class DeleteFromRoundTripTest(fixtures.TablesTest):
+    __backend__ = True
+
+    @classmethod
+    def define_tables(cls, metadata):
+        Table('mytable', metadata,
+              Column('myid', Integer),
+              Column('name', String(30)),
+              Column('description', String(50)))
+        Table('myothertable', metadata,
+              Column('otherid', Integer),
+              Column('othername', String(30)))
+        Table('users', metadata,
+              Column('id', Integer, primary_key=True,
+                     test_needs_autoincrement=True),
+              Column('name', String(30), nullable=False))
+        Table('addresses', metadata,
+              Column('id', Integer, primary_key=True,
+                     test_needs_autoincrement=True),
+              Column('user_id', None, ForeignKey('users.id')),
+              Column('name', String(30), nullable=False),
+              Column('email_address', String(50), nullable=False))
+        Table('dingalings', metadata,
+              Column('id', Integer, primary_key=True,
+                     test_needs_autoincrement=True),
+              Column('address_id', None, ForeignKey('addresses.id')),
+              Column('data', String(30)))
+        Table('update_w_default', metadata,
+              Column('id', Integer, primary_key=True),
+              Column('x', Integer),
+              Column('ycol', Integer, key='y'),
+              Column('data', String(30), onupdate=lambda: "hi"))
+
+    @classmethod
+    def fixtures(cls):
+        return dict(
+            users=(
+                ('id', 'name'),
+                (7, 'jack'),
+                (8, 'ed'),
+                (9, 'fred'),
+                (10, 'chuck')
+            ),
+            addresses=(
+                ('id', 'user_id', 'name', 'email_address'),
+                (1, 7, 'x', 'jack@bean.com'),
+                (2, 8, 'x', 'ed@wood.com'),
+                (3, 8, 'x', 'ed@bettyboop.com'),
+                (4, 8, 'x', 'ed@lala.com'),
+                (5, 9, 'x', 'fred@fred.com')
+            ),
+            dingalings=(
+                ('id', 'address_id', 'data'),
+                (1, 2, 'ding 1/2'),
+                (2, 5, 'ding 2/5')
+            ),
+        )
+
+    @testing.requires.delete_from
+    def test_exec_two_table(self):
+        users, addresses = self.tables.users, self.tables.addresses
+        dingalings = self.tables.dingalings
+
+        with testing.db.connect() as conn:
+            conn.execute(dingalings.delete())  # fk violation otherwise
+
+            conn.execute(
+                addresses.delete().
+                where(users.c.id == addresses.c.user_id).
+                where(users.c.name == 'ed')
+            )
+
+            expected = [
+                (1, 7, 'x', 'jack@bean.com'),
+                (5, 9, 'x', 'fred@fred.com')
+            ]
+        self._assert_table(addresses, expected)
+
+    @testing.requires.delete_from
+    def test_exec_three_table(self):
+        users = self.tables.users
+        addresses = self.tables.addresses
+        dingalings = self.tables.dingalings
+
+        testing.db.execute(
+            dingalings.delete().
+            where(users.c.id == addresses.c.user_id).
+            where(users.c.name == 'ed').
+            where(addresses.c.id == dingalings.c.address_id))
+
+        expected = [
+            (2, 5, 'ding 2/5')
+        ]
+        self._assert_table(dingalings, expected)
+
+    @testing.requires.delete_from
+    def test_exec_two_table_plus_alias(self):
+        users, addresses = self.tables.users, self.tables.addresses
+        dingalings = self.tables.dingalings
+
+        with testing.db.connect() as conn:
+            conn.execute(dingalings.delete())  # fk violation otherwise
+            a1 = addresses.alias()
+            conn.execute(
+                addresses.delete().
+                where(users.c.id == addresses.c.user_id).
+                where(users.c.name == 'ed').
+                where(a1.c.id == addresses.c.id)
+            )
+
+        expected = [
+            (1, 7, 'x', 'jack@bean.com'),
+            (5, 9, 'x', 'fred@fred.com')
+        ]
+        self._assert_table(addresses, expected)
+
+    @testing.requires.delete_from
+    def test_exec_alias_plus_table(self):
+        users, addresses = self.tables.users, self.tables.addresses
+        dingalings = self.tables.dingalings
+
+        d1 = dingalings.alias()
+
+        testing.db.execute(
+            delete(d1).
+            where(users.c.id == addresses.c.user_id).
+            where(users.c.name == 'ed').
+            where(addresses.c.id == d1.c.address_id))
+
+        expected = [
+            (2, 5, 'ding 2/5')
+        ]
+        self._assert_table(dingalings, expected)
+
+    def _assert_table(self, table, expected):
+        stmt = table.select().order_by(table.c.id)
+        eq_(testing.db.execute(stmt).fetchall(), expected)
diff --git a/test/sql/test_update.py b/test/sql/test_update.py
index 71ac82ce0..9ebaddffd 100644
--- a/test/sql/test_update.py
+++ b/test/sql/test_update.py
@@ -391,7 +391,7 @@ class UpdateTest(_UpdateFromTestBase, fixtures.TablesTest, AssertsCompiledSQL):
             where(table1.c.name == sel.c.othername).\
             values(name='foo')
 
-        dialect = default.DefaultDialect()
+        dialect = default.StrCompileDialect()
         dialect.positional = True
         self.assert_compile(
             upd,
@@ -419,7 +419,7 @@ class UpdateTest(_UpdateFromTestBase, fixtures.TablesTest, AssertsCompiledSQL):
 
 class UpdateFromCompileTest(_UpdateFromTestBase, fixtures.TablesTest,
                             AssertsCompiledSQL):
-    __dialect__ = 'default'
+    __dialect__ = 'default_enhanced'
 
     run_create_tables = run_inserts = run_deletes = None