How do you use a WITH clause with a DELETE or UPDATE, and what can the CTE see?

## The shape A `WITH` clause is written before the statement it serves, and that statement does not have to be a `SELECT`: ```sql WITH stale AS ( SELECT session_id FROM sessions WHERE last_seen < DATE '2024-01-01' ) DELETE FROM sessions WHERE session_id IN (SELECT session_id FROM stale); ``` and equivalently for an update: ```sql WITH big_spenders AS ( SELECT customer_id FROM orders GROUP BY customer_id HAVING SUM(total) > 10000 ) UPDATE customers SET tier = 'GOLD' WHERE customer_id IN (SELECT customer_id FROM big_spenders); ``` The CTE does the thinking — aggregation, ranking, several joined steps — and the DML statement does one simple thing with the result. ## Why this beats two statements The obvious alternative is to run a `SELECT`, collect the keys, and send a second statement to delete them. Three things go wrong with that. The qualifying set can change between the two statements, so you act on stale keys. The key list has to travel to the client and back, which is wasteful and can run into statement-size limits. And the intent is split across two places, so a reader of the `DELETE` alone cannot tell what those ids mean. Folding the selection into a `WITH` clause on the same statement removes all three problems: it is one statement, evaluated as a unit. ## The scope rule does not change A CTE attached to a DML statement is still bound to that one statement. You cannot write a `WITH` clause, then a `DELETE`, then an `UPDATE` that reuses the same name; the second statement has never heard of it. If several statements genuinely need the same intermediate set, that is a case for a real object — a table or a temporary table — rather than a CTE. Equally, the CTE is not a snapshot you captured earlier and can reason about separately; it is part of one statement's evaluation. ## Where engines diverge — say less, verify more This is the corner of `WITH` where portability is genuinely thin, and an interviewer will respect "I would check the documentation" far more than a confident wrong claim. Three axes differ: 1. **Whether `WITH` may precede a given DML statement at all.** Support for `INSERT`, `UPDATE` and `DELETE` is common but not universal, and it arrived in different releases. 2. **Whether the statement may read the table it modifies.** Some engines restrict referencing the target table inside the same statement's subqueries, so the delete written above may be accepted by one engine and rejected by another. 3. **Whether a CTE body may itself modify data.** PostgreSQL allows a CTE whose body is an `INSERT`, `UPDATE` or `DELETE` with a `RETURNING` clause, so one statement can move rows between tables. That is a PostgreSQL extension; do not assume it elsewhere. The portable core to rely on is the first pattern in this answer: a read-only CTE that identifies rows, and a single DML statement that acts on them. ## Practical habits Develop the statement as a `SELECT` first — same `WITH` clause, `SELECT * FROM stale` at the end — and inspect what it returns. When the row set is right, swap only the final statement for the `DELETE` or `UPDATE`. Run it inside an explicit transaction while you are still unsure, so a surprising row count can be rolled back. And keep the CTE read-only unless your engine documents otherwise; that keeps the statement understandable to the next reader and portable across engines.