CODEWITHSUNDEEP
sqlsql-serveroutput-clause
Alexander
Alexander

Reputation: 20224

DELETE ... OUTPUT COUNT(DELETED.*)

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?

Upvotes: 8

Views: 21727

Answers (3)

Simone
Simone

Reputation: 1924

Just run your query and get the modified rows

DELETE 
FROM datacache 
WHERE userId=@id

SELECT @@ROWCOUNT

Upvotes: 13

Gordon Linoff
Gordon Linoff

Reputation: 1269683

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.

Upvotes: 2

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

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

Upvotes: 12

Related Questions