- changelog + migration notesticket_2551

2 files changed, 68 insertions, 0 deletions

diff --git a/doc/build/changelog/changelog_11.rst b/doc/build/changelog/changelog_11.rst
index 2473a02a2..273bffb83 100644
--- a/doc/build/changelog/changelog_11.rst
+++ b/doc/build/changelog/changelog_11.rst
@@ -22,6 +22,19 @@
     :version: 1.1.0b1
 
     .. change::
+        :tags: feature, sql
+        :tickets: 2551
+
+        CTE functionality has been expanded to support all DML, allowing
+        INSERT, UPDATE, and DELETE statements to both specify their own
+        WITH clause, as well as for these statements themselves to be
+        CTE expressions when they include a RETURNING clause.
+
+        .. seealso::
+
+            :ref:`change_2551`
+
+    .. change::
         :tags: bug, orm
         :tickets: 3641
 
diff --git a/doc/build/changelog/migration_11.rst b/doc/build/changelog/migration_11.rst
index 3d65ede80..50adf0be0 100644
--- a/doc/build/changelog/migration_11.rst
+++ b/doc/build/changelog/migration_11.rst
@@ -529,6 +529,61 @@ remains unchanged.
 New Features and Improvements - Core
 ====================================
 
+.. _change_2551:
+
+CTE Support for INSERT, UPDATE, DELETE
+--------------------------------------
+
+One of the most widely requested features is support for common table
+expressions (CTE) that work with INSERT, UPDATE, DELETE, and is now implemented.
+An INSERT/UPDATE/DELETE can both draw from a WITH clause that's stated at the
+top of the SQL, as well as can be used as a CTE itself in the context of
+a larger statement.
+
+Below is an example that renders UPDATE, INSERT and SELECT all in one
+statement::
+
+    >>> from sqlalchemy import table, column, select, literal, exists
+    >>> orders = table(
+    ...     'orders',
+    ...     column('region'),
+    ...     column('amount'),
+    ...     column('product'),
+    ...     column('quantity')
+    ... )
+    >>>
+    >>> upsert = (
+    ...     orders.update()
+    ...     .where(orders.c.region == 'Region1')
+    ...     .values(amount=1.0, product='Product1', quantity=1)
+    ...     .returning(*(orders.c._all_columns)).cte('upsert'))
+    >>>
+    >>> insert = orders.insert().from_select(
+    ...     orders.c.keys(),
+    ...     select([
+    ...         literal('Region1'), literal(1.0),
+    ...         literal('Product1'), literal(1)
+    ...     ]).where(~exists(upsert.select()))
+    ... )
+    >>>
+    >>> print(insert)  # note formatting added for clarity
+    WITH upsert AS
+    (UPDATE orders SET amount=:amount, product=:product, quantity=:quantity
+     WHERE orders.region = :region_1
+     RETURNING orders.region, orders.amount, orders.product, orders.quantity
+    )
+    INSERT INTO orders (region, amount, product, quantity)
+    SELECT
+        :param_1 AS anon_1, :param_2 AS anon_2,
+        :param_3 AS anon_3, :param_4 AS anon_4
+    WHERE NOT (
+        EXISTS (
+            SELECT upsert.region, upsert.amount,
+                   upsert.product, upsert.quantity
+            FROM upsert))
+
+:ticket:`2551`
+
 .. _change_3216:
 
 The ``.autoincrement`` directive is no longer implicitly enabled for a composite primary key column