CODEWITHSUNDEEP
sql-server
Ryan
Ryan

Reputation: 7959

Can DELETE FROM be used inside of a CTE?

Can a DELETE FROM, with OUTPUT clause, be used inside of a CTE?

Either it's not allowed or I've got the syntax wrong. It'd be very handy to be able to capture deleted rows in a CTE, like below ...

WITH DeletedItems AS (
  DELETE FROM Foo
  OUTPUT DELETED.*
)
SELECT *
FROM DeletedItems

Redundant example, but it'd be handy to be able to use those deleted items to perhaps further fetch other rows, etc, as part of additional CTEs.

Upvotes: 0

Views: 96

Answers (1)

Pawan Kumar
Pawan Kumar

Reputation: 2021

This is not allowed in SQL Server. If you try you will get:

Incorrect syntax near the keyword 'DELETE'

You can use like below. CTE is not required at all.

CREATE table Foo
(
    Name varchar(10),
    GroupId varchar(2)
)
GO

INSERT INTO Foo VALUES ('A', 'a'), ('B', 'b')
GO

DELETE FROM Foo 
OUTPUT DELETED.*
WHERE GroupId = 'b'

Output:

Name       GroupId
---------- -------
B          b

(1 row affected)

Upvotes: 3

Related Questions