DELETE ... OUTPUT COUNT(DELETED.*)

Question

I want to know how many rows were removed in a certain DELETE operation.

I took the Microsoft example B which is

DELETE Sales.ShoppingCartItem
OUTPUT DELETED.* 
WHERE ShoppingCartID = 20621;

and tried to modify it to return only the count of deleted records:

DELETE FROM datacache 
OUTPUT COUNT(DELETED.*)
WHERE userId=@id

but this throws

ExceptionMessage: "Incorrect syntax near '*'."
ExceptionType: "System.Data.SqlClient.SqlException"
Message: "Error"

So I tried

DELETE FROM datacache 
OUTPUT COUNT(DELETED)
WHERE userId=@id

which throws

ExceptionMessage: "Invalid column name 'DELETED'."
ExceptionType: "System.Data.SqlClient.SqlException"
Message: "Error"

What did I miss?

Answer 1

Just run your query and get the modified rows

DELETE 
FROM datacache 
WHERE userId=@id

SELECT @@ROWCOUNT