Patrick,

My guess would be that when you start at 90 and increase by 10, you step on your own toes because the update is just an update on the 2 numbers which your are changing, not 'where current of cursor' (I couldn't get this to work with an order by clause). So, what you may want to do is to do something like this:


create table junk (a int, b int)
go
truncate table junk
insert junk values(1,1)
insert junk values(1,2)
insert junk values(3,1)
insert junk values(3,4)
insert junk values(3,7)
insert junk values(8,1)
insert junk values(9,1)
insert junk values(9,2)
insert junk values(10,1)
insert junk values(10,4)

select * from junk
/* do it all in a tran to lock the table so as not to mess up others */
begin tran
/* this is new */
update junk set a = a * -1

declare @acurr int, @a1 int, @a2 int, @bcurr int, @b1 int, @b2 int

DECLARE tom_cursor CURSOR FOR
SELECT a,b
FROM junk
order by a desc,b


OPEN tom_cursor

/* set @a2 to 1 instead of -1 as before also set @acurr to 90*/
FETCH NEXT FROM tom_cursor
INTO @a1, @b1
select @a2 = 1, @acurr = 90
WHILE @@FETCH_STATUS = 0
BEGIN
/* increment @acuur by 10 as per the new spec */
if(@a2 != @a1) select @a2 = @a1, @acurr = @acurr +10, @bcurr = 1
else select @bcurr = @bcurr + 1

update junk set a = @acurr, b = @bcurr where a = @a1 and b = @b1
FETCH NEXT FROM tom_cursor
INTO @a1, @b1
END
close tom_cursor
deallocate tom_cursor
select * from junk
commit tran

. . . Tom

Tom Zeblisky




-----------------------------------------------------------------
Visit our Internet site at http://www.reuters.com

Get closer to the financial markets with Reuters Messaging - for more
information and to register, visit http://www.reuters.com/messaging

Any views expressed in this message are those of the individual
sender, except where the sender specifically states them to be
the views of Reuters Ltd.
MS Sql Server LazyDBA home page