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, 17 insertions, 0 deletions

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):