From d12b37f90ef538300f8ebb454eec75beccbe929f Mon Sep 17 00:00:00 2001 From: inytar Date: Mon, 30 Oct 2017 12:01:49 -0400 Subject: 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 Change-Id: I6dfd57b49e44a095d076dc493cd2360bb5d920d3 Pull-request: https://github.com/zzzeek/sqlalchemy/pull/392 Fixes: #959 --- test/dialect/postgresql/test_compiler.py | 16 ++++++++++++++++ 1 file changed, 16 insertions(+) (limited to 'test/dialect/postgresql/test_compiler.py') 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() -- cgit v1.2.1