Solved

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

Posted on 2007-03-27
8
252 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
execute a MS SQL script as a schedule SQL job 72 101
sql calculate averages 18 32
mySQL Syntax 7 0
Join vs where 1 0
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 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