• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

How To Select "(x row(s) affected)" On Update or Delete Statement

I'm trying to figure out what sql syntax to use in order to select the row(s) affected value.

I'm using query analyzer with the following sql:

delete referrals where referralid = 6

When I run this it will say either "(1 row(s) affected)" or "(0 row(s) affected)"

Is there a way I can select that value? Sort of like select scope_identity() when doing an insert statement. Thanks for any help.
0
clickclickbang
Asked:
clickclickbang
4 Solutions
 
Ved Prakash AgrawalDatabase Consultant/Performance ArchitectCommented:
after running any statement @@Rowcount has that information..Like how many rows affected.
0
 
Ved Prakash AgrawalDatabase Consultant/Performance ArchitectCommented:
No..there is no way that  in a single statement you also bind the information that how many rows affect.

@@Rowcount is a goble system variable when the execution complete the information will store in this variable.


0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
What query language are you using?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
SireesCommented:
one alternate way is to count the rows before delete like

select count(*) referrals where referralid = 6

delete referrals where referralid = 6
0
 
clickclickbangAuthor Commented:
I'm using TSQL but I'd like a solution that could be tranportable to MySql.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I'm using TSQL but I'd like a solution that could be tranportable to MySql.
no way, except Sirees' way
0
 
LowfatspreadCommented:
in T-SQL / SQL Server

you can specify the maximum number of rows that can be affected by your  statement

with

Set Rowcount 100
which means only allow 100 "updates"
Set Rowcount 0 means unlimited (the default)

however this limitation is global... and is at the statement level...
so any "updates" processed as a result of triggers firing are also counted...
which is why @@Rowcount does actually give you a Direct count of the Rows affected by the immediate scope
of your initiating statement...

you are recommended to use the TOP X feature in your statements from sql 2000 onwards to limit the numbers
of rows processed...

see Books Online SET ROWCOUNT
 

0
 
clickclickbangAuthor Commented:
Thank you all for the comments. I have plenty to go on now!

~ C
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now