November 6, 2007 at 12:24 pm
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
November 6, 2007 at 1:54 pm
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
November 7, 2007 at 6:32 pm
Thanks for the update and glad it's working.
November 7, 2007 at 7:38 pm
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
Change is inevitable... Change for the better is not.
November 8, 2007 at 6:09 am
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.
November 8, 2007 at 10:30 pm
Heh... thanks for the feedback... I actually wrote a cursor once or twice... it was to show how slow they were...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy