SQL Server CTE with multiple delete statements

Question

I have a CTE:

;WITH DeleteTarget AS
(
    ....
)

How do I use this CTE for two delete statements - maybe like:

DELETE FROM [TableA]
WHERE ColumnA IN (SELECT Id FROM DeleteTarget)

DELETE FROM [TableB]
WHERE ColumnB IN (SELECT Name FROM DeleteTarget)

Answer 1

You cannot - a CTE only exists for the one, next statement.

If you need this information that the CTE provides more than once, you need to:

• store the result set into a table variable or temp table
• then execute your multiple statements using that table variable / temp table