Help with Sequence Renumbering  
Author Message
Woody88





PostPosted: Sat Aug 04 13:40:14 CDT 2007 Top

SQL Server Developer >> Help with Sequence Renumbering

I've got a table that contains a sequence of steps for a given task.

StepId IDENTITY(1,1),
TaskId INT,
StepNumber INT,
StepName VARCHAR(100),
StepText VARCHAR(500),

Basically, StepNumber dictates the order of the steps for this
process. Is there an easy sql statement that I can use to "re-number"
the StepNumber field when a step in the process is deleted?

For example, for these four steps for TaskId 1:
1, 1, 1, Step One Name, Step One Text
2, 1, 2, Step Two Name, Step Two Text
3, 1, 3, Step Three Name, Step Three Text
4, 1, 4, Step Four Name, Step Four Text

If step three is deleted, I'd like the data to read:
1, 1, 1, Step One Name, Step One Text
2, 1, 2, Step Two Name, Step Two Text
4, 1, 3, Original Step Four Name, Original Step Four Text

I'd just like to resequence the StepNumber column for every step
involved in TaskId 1. I'm assuming I have to use a cursor, but I
didn't know if there was a fancier way. I'm using SQL Server 2005.

Thanks

SQL Server119  
 
 
Alex





PostPosted: Sat Aug 04 13:40:14 CDT 2007 Top

SQL Server Developer >> Help with Sequence Renumbering
> I've got a table that contains a sequence of steps for a given task.
>
> StepId IDENTITY(1,1),
> TaskId INT,
> StepNumber INT,
> StepName VARCHAR(100),
> StepText VARCHAR(500),
>
> Basically, StepNumber dictates the order of the steps for this
> process. Is there an easy sql statement that I can use to "re-number"
> the StepNumber field when a step in the process is deleted?
>
> For example, for these four steps for TaskId 1:
> 1, 1, 1, Step One Name, Step One Text
> 2, 1, 2, Step Two Name, Step Two Text
> 3, 1, 3, Step Three Name, Step Three Text
> 4, 1, 4, Step Four Name, Step Four Text
>
> If step three is deleted, I'd like the data to read:
> 1, 1, 1, Step One Name, Step One Text
> 2, 1, 2, Step Two Name, Step Two Text
> 4, 1, 3, Original Step Four Name, Original Step Four Text
>
> I'd just like to resequence the StepNumber column for every step
> involved in TaskId 1. I'm assuming I have to use a cursor, but I
> didn't know if there was a fancier way. I'm using SQL Server 2005.
>
> Thanks

You can just select ROW_NUMBER() OVER(PARTITION BY TaskId ORDER BY
StepNumber) - no need to do anything else.

Alex Kuznetsov, SQL Server MVP
http://sqlserver-tips.blogspot.com/

 
 
--CELKO--





PostPosted: Sat Aug 04 14:15:39 CDT 2007 Top

SQL Server Developer >> Help with Sequence Renumbering >> I've got a table that contains a sequence of steps for a given task. <<

Please post DDL, so that people do not have to guess what the keys,
constraints, declarative referential integrity, data types, etc. In
your schema are. IDENTITY has to go so you can get a real key, you
need constraints, etc. Try this skeleton:

CREATE TABLE Process -- only one?
(task_id INTEGER NOT NULL
CHECK(task_id > 0),
step_nbr INTEGER NOT NULL
CHECK(step_nbr > 0),
PRIMARY KEY (task_id, step_nbr),
step_name VARCHAR(100) NOT NULL,
step_text VARCHAR(500) NOT NULL)

>> Is there an easy SQL statement that I can use to "re-number the step_nbr field [sic] when a step in the process is deleted? <<


AS
UPDATE Process
SET step_nbr
= (SELECT COUNT(*)
FROM Process AS P1
WHERE P1.step_nbr <= Process.step_nbr
AND P1.task_id = Process.task_id)


Here is a cut & paste about a more general statement for re-arranging
your steps:

============
Given a motorpool with numbered parking spaces, you want to move the
automobiles around.

CREATE TABLE Motorpool
(parking_space INTEGER NOT NULL PRIMARY KEY
CHECK (parking_space > 0),
vin CHAR(17) NOT NULL);

Re-arrange the display order based on the parking_space column:


@new_parking_space INTEGER)
AS
UPDATE Motorpool
SET parking_space
= CASE parking_space



END

@new_parking_space

@old_parking_space;

When you want to drop a few rows, remember to close the gaps with
this:

CREATE PROCEDURE CloseMotorpoolGaps()
AS
UPDATE Motorpool
SET parking_space
= (SELECT COUNT (M1.parking_space)
FROM Motorpool AS M1
WHERE M1.parking_space <= Motorpool.parking_space);

 
 
Uri





PostPosted: Sun Aug 05 02:29:37 CDT 2007 Top

SQL Server Developer >> Help with Sequence Renumbering Hi
I'd suggest you using the following tecnique. What if many people at the
same tine are pefroming deletion /updating.

create table seq(val int not null);
insert into seq values(0);
go
select * from seq

as


from seq with (tablock); --- to prevent deadlocks

go







> I've got a table that contains a sequence of steps for a given task.
>
> StepId IDENTITY(1,1),
> TaskId INT,
> StepNumber INT,
> StepName VARCHAR(100),
> StepText VARCHAR(500),
>
> Basically, StepNumber dictates the order of the steps for this
> process. Is there an easy sql statement that I can use to "re-number"
> the StepNumber field when a step in the process is deleted?
>
> For example, for these four steps for TaskId 1:
> 1, 1, 1, Step One Name, Step One Text
> 2, 1, 2, Step Two Name, Step Two Text
> 3, 1, 3, Step Three Name, Step Three Text
> 4, 1, 4, Step Four Name, Step Four Text
>
> If step three is deleted, I'd like the data to read:
> 1, 1, 1, Step One Name, Step One Text
> 2, 1, 2, Step Two Name, Step Two Text
> 4, 1, 3, Original Step Four Name, Original Step Four Text
>
> I'd just like to resequence the StepNumber column for every step
> involved in TaskId 1. I'm assuming I have to use a cursor, but I
> didn't know if there was a fancier way. I'm using SQL Server 2005.
>
> Thanks
>