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.
LVL 1
clickclickbangAsked:
Who is Participating?
 
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
 
Ved Prakash AgrawalDatabase Consultant/Performance ArchitectCommented:
after running any statement @@Rowcount has that information..Like how many rows affected.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
What query language are you using?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.