SQL Server › Common Table Expressions (CTEs)

Can a common table expression be used inside an UPDATE, DELETE, or MERGE statement, and how does that work?

Advanced

Answer

Yes, you can define a CTE and then reference it directly within an UPDATE, DELETE, or MERGE statement, which is particularly useful when you need to first identify a specific, often complex, set of rows using logic like window functions or joins, and then perform a data modification against exactly those identified rows.

WITH RecentHighValue AS (
  SELECT OrderId FROM Orders
  WHERE Amount > 1000 AND OrderDate >= '2026-01-01'
)
UPDATE Orders
SET Status = 'Priority'
WHERE OrderId IN (SELECT OrderId FROM RecentHighValue);

Real-world example An order management system marks all high value recent orders as priority using a CTE to first clearly identify exactly which orders qualify, then applies the update to just those specific rows.

Common follow-ups: Does using a CTE in an UPDATE statement affect performance compared to a plain subquery?;Can the same CTE be referenced by both a SELECT and an UPDATE in the same statement?

Merge Statement (Upsert);Subqueries