DELETE ... OUTPUT COUNT(DELETED.*)

Question

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

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?

A classic way would be to `SELECT @@ROWCOUNT` which should show you the number of affected rows. — Micke, Jun 15 '15 at 11:42
Please see the link..http://www.sql-server-helper.com/error-messages/msg-158.aspx — arunbabu, Jun 15 '15 at 11:43

score 11 · Answer 1 · answered Jun 15 '15 at 11:43

You can not use aggregates in OUTPUT clause. You can output any column into table variable instead and count from there:

DECLARE @t TABLE(id int)

DELETE FROM Sales.ShoppingCartItem
OUTPUT Deleted.ShoppingCartID INTO @t
WHERE ShoppingCartID = 20621;

SELECT COUNT(*) FROM @t

score 11 · Accepted Answer · answered Jun 15 '15 at 11:43

11

Just run your query and get the modified rows

DELETE 
FROM datacache 
WHERE userId=@id

SELECT @@ROWCOUNT

answered Jun 15 '15 at 11:43

Simone

1,828
1
13
20

score 1 · Answer 3 · answered Jun 15 '15 at 11:43

1

How about counting the records afterwards?

DELETE Sales.ShoppingCartItem
OUTPUT DELETED.ID INTO @DELETEDIDS
WHERE ShoppingCartID = 20621;

SELECT COUNT(*)
FROM @DELETEDIDS;

Or, just run the query and use @@ROWCOUNT.

answered Jun 15 '15 at 11:43

Gordon Linoff

1,242,037
58
646
786

DELETE ... OUTPUT COUNT(DELETED.*)

3 Answers3