I apologize for the delay in getting back to the group with my
findings....but here they are:
Tom, you gave me exactly what I asked for. Works perfect! However(there
always is one, isn't there?), the requirements have changed.......they want
me to start the steps at '100' and increase by 10.
I quickly modified the code to set @acurr = 90 and to increase by 10.
Quickly errored out as there is a clustered pk on both of these columns.
So, I removed the pk, then applied the changes, then ran it again. It ran
successfully, however, it is now not producing desired results. I'm not
sure how to explain the problem because I don't understand it fully, but the
re-sequenced numbers start fine, then after awhile, become all out of order.
I specified asc for a and b.....and still not good results.
-----Original Message-----
From: thomas.[Email Address Removed] [mailto:thomas.[Email Address Removed] Thursday, April 24, 2003 2:25 PM
To: LazyDBA.com Discussion
Subject: Re: re-sequence steps
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.
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To unsubscribe, e-mail: mssqldba-[Email Address Removed] additional commands, e-mail: mssqldba-[Email Address Removed]MS Sql Server LazyDBA home page