DELETE ... OUTPUT COUNT(DELETED.*)

sql sql-server output-clause
Alexander picture Alexander · Jun 15, 2015 · Viewed 13.7k times · Source

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

Simone picture Simone · Jun 15, 2015

Just run your query and get the modified rows

DELETE 
FROM datacache 
WHERE userId=@id

SELECT @@ROWCOUNT

Related questions

@@IDENTITY, SCOPE_IDENTITY(), OUTPUT and other methods of retrieving last identity

I have seen various methods used when retrieving the value of a primary key identity field after insert. declare @t table ( id int identity primary key, somecol datetime default getdate() ) insert into @t default values select SCOPE_IDENTITY() --returns 1 select @@…

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More