Deleting from a CTE with an EXISTS statement

Download PDF

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:

CteDelet1

 

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.

CteDelet3w

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.

Tagged with: , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Time limit is exhausted. Please reload CAPTCHA.