During my 24 Hours of Pass presentation on Advanced CTE’s today I was asked the question about deleting from a CTE when it uses an EXISTS statement that queries another table. I figured I would create quick blog post to show the example.
First some background when using the delete statement with a CTE you can’t delete if the query inside of a CTE references multiple tables. For instance:
;WITH CustomerCTE AS ( SELECT c.* FROM Customer AS c INNER JOIN SalesInvoice AS si ON si.CustomerID = c.CustomerID WHERE c.LastName like 'Williams' ) DELETE FROM CustomerCTE;
Will return an error like this:
An the question was asked around referencing a second table using EXISTS, for instance:
;WITH CustomerCTE AS ( SELECT c.* FROM Customer AS c WHERE EXISTS (SELECT 1 FROM SalesInvoice AS si WHERE si.CustomerID = c.CustomerID AND c.LastName like 'Williams') ) DELETE FROM CustomerCTE;
Since the CTE using the EXISTS example only references one table in the CTE query (technically the query in the exists is a correlated subquery or derived table), you are able to do it just fine.
So if you can restructure a query inside of a CTE into an EXIST clause being used for DELETE, you can make it work as a way to get around the single table in a CTE delete statement error.
Thanks for asking.