Hi Patrick,

Here is an example. You will have to modify it for your situation - changing table and column names and changing the where clause in the update statement to reflect the proper primary key. There are potential concurrency problems if others can modify this table. If this is the case, you may want to wrap the whole thing in:
Begin tran
update mytable set step = step
code to reorg the steps goes here
commit tran
By doing this, you will either lock all the rows in the table or escalate to a table lock but either way, you don't need to worry about concurrency.

The example that I tried is a little cryptic but should be readable. If you have questions, you can write me offgroup:

--drop table junk
create table junk (a int, b int)
go
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

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


OPEN tom_cursor

FETCH NEXT FROM tom_cursor
INTO @a1, @b1
select @a2 = -1, @acurr = 0
WHILE @@FETCH_STATUS = 0
BEGIN
if(@a2 != @a1) select @a2 = @a1, @acurr = @acurr +1, @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


You may want to lock the table On 04/24/2003 11:23:25 AM "Conahan, Patrick" wrote:
> I have a table that looks like this....there's other columns as well. I
> need to re-sequence these step and sub_step numbers. How can I use a
> cursor the get the desired affect? Any help would be greatly
> appreciated....
>
> what I have now:
> step sub_step
> 1 1
> 1 2
> 3 1
> 3 2
> 3 4
> 4 1
> 4 3
>
> what I need:
> step sub_step
> 1 1
> 1 2
> 2 1
> 2 2
> 2 3
> 3 1
> 3 2
>
> Patrick Conahan
>



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