allow CTE to be direct DML target

Implemented support for a :class:`_sql.CTE` construct to be used directly
as the target of a :func:`_sql.delete` construct, i.e. "WITH ... AS cte
DELETE FROM cte". This appears to be a useful feature of SQL Server.

The CTE is now generally usable as a DML target table however
it's not clear if this syntax is valid beyond the use case of
DELETE itself.

Fixes: #6464
Change-Id: I3aac6fae2a1abb39bc0ffa87a044f5eb4f90f026

1 files changed, 7 insertions, 1 deletions

diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py
index 43ba0da4c..7007bb430 100644
--- a/lib/sqlalchemy/sql/selectable.py
+++ b/lib/sqlalchemy/sql/selectable.py
@@ -1992,7 +1992,13 @@ class TableSample(AliasedReturnsRows):
             return functions.func.system(self.sampling)
 
 
-class CTE(Generative, HasPrefixes, HasSuffixes, AliasedReturnsRows):
+class CTE(
+    roles.DMLTableRole,
+    Generative,
+    HasPrefixes,
+    HasSuffixes,
+    AliasedReturnsRows,
+):
     """Represent a Common Table Expression.
 
     The :class:`_expression.CTE` object is obtained using the