cascading the order of rows - 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)

cascading the order of rows

Author

Topic

TypeDef Enum
Starting Member

3 Posts

Posted - 2005-05-10 : 20:00:35

Hello,

I have a small problem I encountered while trying to do some DML on a table. The table in question is called Units. In this table; units belong to courses. There can be an infinite number of courses and also an infinite number of units for a single course. But from what I have been told a max of 30 units per course. The problem I am having is if the user wishes to change the order in which the units should be displayed. When a unit is created the column [Order] is assigned a sequential number e.g. I created a new unit for a brand new course so it will be assigned the number 1. When there are 10 rows for a single course and all in sequential order from 1-10 and I want to make unit with the order number 5 become order 1 what would be the best way of accomplishing this update. Keep in mind that changing the order of one unit cascades and will change all so to keep the ordering structure intact. It would seem a recursive pattern would first come to my mind in order to solve this problem, but recursion is meant more for application programming not databases. I look forward to a solution from all or any. I have included the DDL and dummy data for you to play with. Thanks

CREATE TABLE [Unit] (
[UnitID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
[CourseID] [int] NOT NULL ,
[Name] [varchar] (255) NOT NULL ,
[Comments] [varchar] (512) NOT NULL ,
[Status] [bit] NOT NULL ,
[Order] [smallint] NOT NULL ,
[Date] [smalldatetime] NOT NULL
)
GO

DECLARE @i int
SET @i = 1
WHILE @i <= 30 BEGIN
INSERT dbo.Unit (CourseID, [Name], Comments, Status, [Order], [Date])
VALUES(1, 'Unit - ' + CAST(@i as varchar(2)),'My comments', 1, @i, GETDATE())
SET @i = @i + 1
END

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-05-10 : 20:21:55

Not quite enough information...

If Order 5 and 1 swap...


declare @Old INT, @New INT
SELECT @old = 1, @new = 5
update unit set [Order] = CASE WHEN [Order] = @Old then @New
				WHEN [Order] = @New then @Old ELSE [Order] end
where courseid = 1

DavidM

A front-end is something that tries to violate a back-end.

TypeDef Enum
Starting Member

3 Posts

Posted - 2005-05-10 : 20:39:01

Thanks that is what I was after. Soon as I read my own post I new all I had to do what a swap I don't know what I was thinking before, having a brain fart I guess. Anyways cheers for the help and quick response.

quote:
Originally posted by byrmol

Not quite enough information...

If Order 5 and 1 swap...

declare @Old INT, @New INT
SELECT @old = 1, @new = 5
update unit set [Order] = CASE WHEN [Order] = @Old then @New
WHEN [Order] = @New then @Old ELSE [Order] end
where courseid = 1

DavidM

A front-end is something that tries to violate a back-end.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources