Re-Sequence Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Re-Sequence Problem

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-12-09 : 08:57:46

Jon writes "Summary:
I have a table that has a sequence number, not autogenerated, that I have to be able to re-sequence.

Details:
I have orders that are moved around in a schedule for a particular date and once they are moved I need the sequence numbers renumbered.

Example:
I choose to change the status of a product to "Move" and place before, which ever product it needs to be moved before. It will take the products that it needs to be moved befores current sequence number (5) and subtract .1 from it and assign a new sequence number of say 4.9 to the moved product. I have the table order by the date then by sequence number so the person pulling products knows whats next. There may be 300 orders per day that is sequenced from 1 - 300 per date field.

Conclusion:
My problem is I can't move anything else between seq # 5 and 4.9 without re-sequencing the table by date, obvious enough?

I tried searching your site but I'm at a loss. Do I need to use a temp table dump everything to it and then resequence and dump back?"

X002548
Not Just a Number

15586 Posts

Posted - 2004-12-09 : 09:16:15

Yup, clear as mud..

what about 4.95?

Brett

8-)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-12-09 : 09:29:32

Lets say you have products p01 through p10

Like So:
orderId: 01 02 03 04 05 06 07 08 09 10
prodid: p1 p2 p3 p4 p5 p6 p7 p8 p9 p10

and lets say you want to move p9 in between p3 and p4

orderId: 01 02 03 04 05 06 07 08 09 10
prodid: p1 p2 p3 p9 p4 p5 p6 p7 p8 p10

So what you said in SQL was:


Declare @myTable table (pId varchar(5), orderId int)

Insert Into @myTable
Select 'p01', 1
Union Select 'p02', 2
Union Select 'p03', 3
Union Select 'p04', 4
Union Select 'p05', 5
Union Select 'p06', 6
Union Select 'p07', 7
Union Select 'p08', 8
Union Select 'p09', 9
Union Select 'p10', 10

--Select * From @myTable

Declare @movePId varchar(5),
	@inFrontOfPId varchar(5)

Set @movePId = 'p09'
Set @inFrontOfPId = 'p04'

Select 
	pId,
	orderId,
	newOrderId = case 
			when pId = @movePId then 
				(Select orderId From @myTable Where pId = @inFrontOfPId)
			Else orderId + 1 end
From @myTable 
Where orderId between (Select orderId From @myTable Where pId = @inFrontOfPId) and (Select orderId From @myTable Where pId = @movePId)

--Use that as an update... and Viola! 

Update @myTable
Set 
	orderId = case 
			when pId = @movePId then 
				(Select orderId From @myTable Where pId = @inFrontOfPId)
			Else orderId + 1 end
From @myTable 
Where orderId between (Select orderId From @myTable Where pId = @inFrontOfPId) and (Select orderId From @myTable Where pId = @movePId)


Select * From @myTable Order By OrderId

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources