Having problem deleting through a cursor - newbie

Question

Having problem deleting through a cursor - newbie

toniupstny

Hall of Fame

Points: 3986
More actions
November 6, 2007 at 12:24 pm

#200231

Hi. I realize I am probably doing something very dumb here being new to this. In trying out the cursor functions I tried to remove dup records from a table. I realize there is an easier way to remove duplicate records but can you tell me why this doesn't seem to work?
I can't seem to be able to declare an update cursor and the delete stmt says "The cursor is READ ONLY."
use pubs
go
Declare @Pub_id char(4), @pub_name varchar(40),
@city varchar(20), @state char(2), @country varchar(30),
@sv_Pub_id char(4), @sv_pub_name varchar(40),
@sv_city varchar(20), @sv_state char(2), @sv_country varchar(30),
@tot_rows int, @cur_row int
declare my_cursor cursor
scroll scroll_locks
for select pub_id, pub_name, city, state, country from publishers2
/* for update - this gets: FOR UPDATE cannot be specified on a READ ONLY cursor. */
select * from publishers2
Set @tot_rows = @@ROWCOUNT -- save total_rows
open my_cursor
Select 'total_rows ',@tot_rows, 'Rowcount is ', @@ROWCOUNT
/* get the first row */
set @cur_row = 1
FETCH Absolute @cur_row FROM my_cursor
INTO @sv_Pub_id, @sv_pub_name, @sv_city, @sv_state , @sv_country
WHILE @@FETCH_STATUS = 0 and @cur_row < @tot_rows /* loop through table looking for DUPs */
BEGIN
FETCH NEXT FROM my_cursor INTO @Pub_id, @pub_name,
@city, @state, @country
WHILE @@FETCH_STATUS = 0
BEGIN /* remove duplicate rows */
IF @sv_pub_id = @pub_id and @sv_Pub_name = @sv_pub_name and @sv_city = @city and
@sv_state = @state and @sv_country = @country
begin
delete from publishers2 where current of my_cursor
set @tot_rows = @tot_rows -1
end
FETCH NEXT FROM my_cursor INTO @Pub_id, @pub_name ,
@city, @state, @country
End
Set @cur_row = @cur_row + 1 /* point to the next row in table */
FETCH absolute @cur_row FROM my_cursor
INTO @sv_Pub_id, @sv_pub_name, @sv_city, @sv_state , @sv_country
End
CLOSE my_cursor
DEALLOCATE my_cursor

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

toniupstny Hall of Fame Points: 3986 More actions · Answer 1

ok.... it turns out that you have to have a UNIQUE index on the table in order for the cursor to be UPDATE. I have other problems with my script but can work these out the usual way.

Thank you to anyone who spent the time checking into this

Toni

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 2

Steve Jones - SSC Editor

SSC Guru

Points: 734449

November 7, 2007 at 6:32 pm

#749300

Thanks for the update and glad it's working.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

I've not wallowed through the labyrinth of your cursor code to see exactly what you're doing, but why are you using cursors to do a simple dupe check/delete? There are 3 or 4 proven set based methods to do this and they'll blow the cursor method out of the water for performance...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

toniupstny Hall of Fame Points: 3986 More actions · Answer 4

I agree that cursors are overkill. I was looking for a way to use cursors as part of learning about them is all Anyway, thanks for the response and good thing you didn't wade through the code, it needed a bunch of work before getting it right once I got past the initial problem.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

Heh... thanks for the feedback... I actually wrote a cursor once or twice... it was to show how slow they were... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)