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?
SELECT @@ROWCOUNT
which should show you the number of affected rows. – Berkley