SQL Server CTE with multiple delete statements

user_7324189 0
Viewed 1520

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)
common-table-expression sql-server-2016
1 Answers

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
user_133020
Related