DELETE ... OUTPUT COUNT(DELETED.*)
Asked Answered
V

3

8

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?

Veneration answered 15/6, 2015 at 11:37 Comment(3)
A classic way would be to SELECT @@ROWCOUNT which should show you the number of affected rows.Berkley
Please see the link..sql-server-helper.com/error-messages/msg-158.aspxElul
Did any of the answers solved your issue?Ageratum
A
13

Just run your query and get the modified rows

DELETE 
FROM datacache 
WHERE userId=@id

SELECT @@ROWCOUNT
Ageratum answered 15/6, 2015 at 11:43 Comment(0)
E
12

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
Essy answered 15/6, 2015 at 11:43 Comment(0)
K
2

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.

Kookaburra answered 15/6, 2015 at 11:43 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.