Solved

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

Posted on 2007-03-27
8
254 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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
 
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

Title # Comments Views Activity
Need help how to find where my error is in UFD 6 30
SQL Improvement  ( Speed) 14 28
VB.NET 2008 - SQL Timeout 9 25
T-SQL Default value in Select? 5 28
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.
I have a large data set and a SSIS package. How can I load this file in multi threading?
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

832 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