Renumber integer value - 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

Transact-SQL (2000)

Renumber integer value

Author

Topic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-14 : 17:28:18

I am hoping someone can help me with a set-based approach to this problem. We already have a WHILE loop solution. I'll provide it below to show what we have.


NMCAccount  TractorID  LoadID StopNumber  PlaceCode  
----------- ---------- ------ ----------- ---------- 
10000       Tractor1   Load1  1           23
10000       Tractor1   Load1  2           11
10000       Tractor1   Load1  3           45
10000       Tractor1   Load1  4           13
10000       Tractor1   Load1  99          52

The first four columns are the primary key. The application requirement is that if a user wants to insert a StopNumber with a value of 2, we have to bump up 2, 3, 4 so that they become 3, 4, 5 respectively. Expected result set using the above sample data:


NMCAccount  TractorID  LoadID StopNumber  PlaceCode  
----------- ---------- ------ ----------- ---------- 
10000       Tractor1   Load1  1           23
10000       Tractor1   Load1  2           76
10000       Tractor1   Load1  3           11
10000       Tractor1   Load1  4           45
10000       Tractor1   Load1  5           13
10000       Tractor1   Load1  99          52

In the above result set, I added StopNumber 2 with PlaceCode of 76. Notice how the old 2 row became 3, the old 3 row became 4, the old 4 row became 5, and the 99 row wasn't touched since the other rows fit already.

DDL and DML:


CREATE TABLE [dbo].[Stop](
	[NMCAccount] [int] NOT NULL,
	[TractorID] [varchar](10) NOT NULL,
	[LoadID] [varchar](5) NOT NULL,
	[StopNumber] [int] NOT NULL,
	[PlaceCode] [varchar](10)NOT NULL,
 CONSTRAINT [PK_Stop] PRIMARY KEY CLUSTERED 
(
	[NMCAccount] ASC,
	[TractorID] ASC,
	[LoadID] ASC,
	[StopNumber] ASC
)
) ON [PRIMARY]
GO

INSERT INTO [dbo].[Stop]([NMCAccount], [TractorID], [LoadID], [StopNumber], [PlaceCode])
SELECT 	10000, 'Tractor1', 'Load1', 1, '23' UNION ALL
SELECT 	10000, 'Tractor1', 'Load1', 2, '11' UNION ALL
SELECT 	10000, 'Tractor1', 'Load1', 3, '45' UNION ALL
SELECT 	10000, 'Tractor1', 'Load1', 4, '13' UNION ALL
SELECT 	10000, 'Tractor1', 'Load1', 99, '52'
GO

Here is the solution that the developer came up with. It is using SQL Server 2005 logic.


CREATE  PROCEDURE [dbo].[usp_StopRenumber]
(@NMCAccount int, @LoadID varchar(20), @StopNumber int)
AS

--declare variables
DECLARE @Stop int, @Count int, @StopNo int

--initailize variables
SELECT @Stop = NULL, @Count = 0, @StopNo = 0

--Create a temp table
CREATE TABLE #TempStops(StopNumber int)

--Gather all the stop numbers that need to be renumbered
BEGIN TRY
	WHILE (1=1)
	BEGIN
		  SELECT @Stop = StopNumber 
		  FROM Stop 
		  WHERE 
			NMCAccount = @NMCAccount AND 
			LoadId = @LoadId AND StopNumber = @StopNumber

		  --If no rows were selected then break out else try the next incremented stop number
		  IF ISNULL(@Stop,0) = 0 
				BREAK
		  ELSE
		  BEGIN 
				--Insert this stop number into the temp table
				INSERT INTO #TempStops(StopNumber) VALUES(@Stop)
				SELECT @StopNumber = @StopNumber + 1, @Stop = null
		  END
	END
	--Obtain the number of records in the temp stop table.
	SELECT @Count = Count(*) FROM #TempStops
	 
	--If the temp table does have records, then increment the stop numbers of the stops in the temp table
	WHILE @Count > 0
	BEGIN
		  --select the largest stop number
		  SELECT TOP 1 @StopNo = StopNumber FROM #TempStops ORDER BY StopNumber DESC

		  --Increment it by 1
		  UPDATE Stop 
		  SET StopNumber = StopNumber + 1 
		  WHERE NmcAccount = @NMCAccount AND LoadId = @LoadId AND StopNumber = @StopNo

		  --Delete it from the temp table
		  DELETE #TempStops WHERE StopNumber = @StopNo 

		  --Decrement the count
		  SET @Count = @Count - 1
	END
END TRY

BEGIN CATCH
      RETURN 0
END CATCH
GO

I can provide SQL Server 2000 for the above as well. I just didn't want to make the post too long.

Here's how to see that the stored procedure works:


DECLARE @NMCAccount int, @TractorID varchar(50), @LoadID varchar(20), @StopNumber int, @PlaceCode varchar(50), @err int

SELECT @NMCAccount = 10000, @TractorID = 'Tractor1', @LoadID = 'Load1', @StopNumber = 2, @PlaceCode = '76'

--insert the stop
BEGIN TRY
	INSERT Stop (NMCAccount, TractorID, LoadID, StopNumber, PlaceCode)
	VALUES (@NMCAccount, @TractorID, @LoadID, @StopNumber, @PlaceCode)
END TRY

BEGIN CATCH
	--If the stop number already exists, then renumber the other stops and try the insert again
	SET @err = @@ERROR

	IF @err = 2627 --Primary Key Violation
	BEGIN
		EXEC usp_StopRenumber @NMCAccount, @LoadID, @StopNumber
		INSERT Stop (NMCAccount, TractorID, LoadID, StopNumber, PlaceCode)
		VALUES (@NMCAccount, @TractorID, @LoadID, @StopNumber, @PlaceCode)
	END
END CATCH
GO

SELECT * FROM Stop

Clean-up:


DROP TABLE Stop
DROP PROC usp_StopRenumber

Note: I do have a Numbers table in this environment.

Tara Kizer

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-09-14 : 17:48:21

Is it a requirement that the 99 *must* remain 99, or can it be incremented to 100? Does 99 have any special meaning or is it just an example of a gap in the sequence ?

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-14 : 18:03:42

I have left a voice mail with the developer to find out. I had assumed that it had special meaning, but now I'm not sure. If it doesn't have special meaning, then this should work:


IF @err = 2627 --Primary Key Violation
BEGIN
	UPDATE STOP
	SET StopNumber = StopNumber + 1
	WHERE 
		NMCAccount = @NMCaccount AND
		TractorID = @TractorID AND
		LoadID = @LoadId AND	
		StopNumber >= @StopNumber 

	INSERT Stop (NMCAccount, TractorID, LoadID, StopNumber, PlaceCode)
	VALUES (@NMCAccount, @TractorID, @LoadID, @StopNumber, @PlaceCode)
END

I'll let you know soon.

Tara Kizer

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-14 : 20:00:23

This seems to take care of it, and only update rows up to the start of the next gap in the StopNumber


print 'Before Update'
select * from  [dbo].[Stop]


print 'Do Update'
update  [dbo].[Stop]
set
	StopNumber = StopNumber +1
where
	NMCAccount = 10000 and
	TractorID = 'Tractor1' and
	LoadID  = 'Load1' and
	StopNumber >= 2 and
	StopNumber <= (
	select
		min(b1.StopNumber)
	from
		[dbo].[Stop] b1
		left join
		[dbo].[Stop] b2
		on
		b1.NMCAccount = b2.NMCAccount and
		b1.TractorID =b2.TractorID and
		b1.LoadID  = b2.LoadID and
		b1.StopNumber = b2.StopNumber-1
	where
		b1.NMCAccount = 10000 and
		b1.TractorID = 'Tractor1' and
		b1.LoadID  = 'Load1' and
		b1.StopNumber >= 2 and
		b2.StopNumber is null
	)

print 'After Update'
select * from  [dbo].[Stop]

Results:


Before Update
NMCAccount  TractorID  LoadID StopNumber  PlaceCode  
----------- ---------- ------ ----------- ---------- 
10000       Tractor1   Load1  1           23
10000       Tractor1   Load1  2           11
10000       Tractor1   Load1  3           45
10000       Tractor1   Load1  4           13
10000       Tractor1   Load1  99          52

(5 row(s) affected)

Do Update

(3 row(s) affected)

After Update
NMCAccount  TractorID  LoadID StopNumber  PlaceCode  
----------- ---------- ------ ----------- ---------- 
10000       Tractor1   Load1  1           23
10000       Tractor1   Load1  3           11
10000       Tractor1   Load1  4           45
10000       Tractor1   Load1  5           13
10000       Tractor1   Load1  99          52

(5 row(s) affected)

CODO ERGO SUM

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-14 : 20:11:10

Excellent, Michael!

I still haven't heard back from the developer yet, but at least I have an answer whichever way she responds. It certainly beats the existing solution.

Tara Kizer

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-14 : 20:25:07

I hope there aren’t any foreign key references to that table. If there are, you may want to redesign the table with a surrogate primary key, and a unique constraint on the four columns in the current primary key.

I prefer primary keys that never change once a row is inserted.

CODO ERGO SUM

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-15 : 02:23:46

Yeah me too. This one went in without my permission. I have told this team countless times that updating the primary key is bad design.

I believe they have two tables where they update the primary key. It drives me nuts!

Tara Kizer

Kristen
Test

22859 Posts

Posted - 2006-09-15 : 04:27:34

"at least I have an answer whichever way she responds"

probably better to go with the "increment until the next stop gap" anyway, isn't it? Fewer rows get updated that way ...

... of course if 99 IS a special value you'd then need a check that the table wasn't full up to that point.

Developers eh? Pah!

I have to say we normally do this by selecting all the rows that need renumbering into a @TempTable with an Identity column, and then update the original table JOINed to the @TempTable to use the Identity column as a basis for the new renumbered value. But usually we are doing this in a table with sparse numbers where the gaps are used up / getting used up, and we want to renumber with consistent gaps for future expansion - whereas looks like you are just needing to bump up the values.

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-09-15 : 10:15:23

quote:

Originally posted by Michael Valentine Jones

This seems to take care of it, and only update rows up to the start of the next gap in the StopNumber


print 'Before Update'
select * from  [dbo].[Stop]


print 'Do Update'
update  [dbo].[Stop]
set
	StopNumber = StopNumber +1
where
	NMCAccount = 10000 and
	TractorID = 'Tractor1' and
	LoadID  = 'Load1' and
	StopNumber >= 2 and
	StopNumber <= (
	select
		min(b1.StopNumber)
	from
		[dbo].[Stop] b1
		left join
		[dbo].[Stop] b2
		on
		b1.NMCAccount = b2.NMCAccount and
		b1.TractorID =b2.TractorID and
		b1.LoadID  = b2.LoadID and
		b1.StopNumber = b2.StopNumber-1
	where
		b1.NMCAccount = 10000 and
		b1.TractorID = 'Tractor1' and
		b1.LoadID  = 'Load1' and
		b1.StopNumber >= 2 and
		b2.StopNumber is null
	)

print 'After Update'
select * from  [dbo].[Stop]

Results:


Before Update
NMCAccount  TractorID  LoadID StopNumber  PlaceCode  
----------- ---------- ------ ----------- ---------- 
10000       Tractor1   Load1  1           23
10000       Tractor1   Load1  2           11
10000       Tractor1   Load1  3           45
10000       Tractor1   Load1  4           13
10000       Tractor1   Load1  99          52

(5 row(s) affected)

Do Update

(3 row(s) affected)

After Update
NMCAccount  TractorID  LoadID StopNumber  PlaceCode  
----------- ---------- ------ ----------- ---------- 
10000       Tractor1   Load1  1           23
10000       Tractor1   Load1  3           11
10000       Tractor1   Load1  4           45
10000       Tractor1   Load1  5           13
10000       Tractor1   Load1  99          52

(5 row(s) affected)

CODO ERGO SUM

Exactly what I was thinking. That should work fine.

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-15 : 12:11:07

According to the developer, the 99 does have special meaning, so I can't increment it by 1. I will pass Michael's solution on to her.

Thanks to both of you!

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources