Solved

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

Posted on 2007-03-27
8
253 Views
Last Modified: 2012-05-05
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
Comment
Question by:clickclickbang
8 Comments
 
LVL 11

Expert Comment

by:Ved Prakash Agrawal
ID: 18799931
after running any statement @@Rowcount has that information..Like how many rows affected.
0
 
LVL 11

Accepted Solution

by:
Ved Prakash Agrawal earned 200 total points
ID: 18799953
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 18799974
What query language are you using?
0
 
LVL 20

Assisted Solution

by:Sirees
Sirees earned 150 total points
ID: 18800009
one alternate way is to count the rows before delete like

select count(*) referrals where referralid = 6

delete referrals where referralid = 6
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 1

Author Comment

by:clickclickbang
ID: 18800052
I'm using TSQL but I'd like a solution that could be tranportable to MySql.
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 50 total points
ID: 18800116
>I'm using TSQL but I'd like a solution that could be tranportable to MySql.
no way, except Sirees' way
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 100 total points
ID: 18800688
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
 
LVL 1

Author Comment

by:clickclickbang
ID: 18808119
Thank you all for the comments. I have plenty to go on now!

~ C
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now