Increment/Decrement Help 2

BG12424 · Jan 5, 2005

I have a table with a column designated for ordering page content. I was wondering what SQL I could use to auto-increment/auto-decrement order numbers in a column. For example if my columns looked like this:

Code:

PageId PageOrder
------ ----------
1      1 
2      2
3      3
4      4
5      5
6      6
7      7

...and I want to change the page order for pageId 3 to be 5, then all the page orders greater than pageorder of 3 will be decremented until the updated pageorder of 5. This will now look like this:

Code:

PageId PageOrder
------ ----------
1      1 
2      2
3      5
4      3
5      4
6      6
7      7

Can someone please help with this? Thank you

regards,
Brian

http://www.gainesconsulting.com

The problem with doing something right the first time is that nobody appreciates how difficult it was.

donutman · Jan 5, 2005

Do you want to generalize this in some way? You can't mean that you want to just make that one change???
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

BG12424 · Jan 5, 2005

No, you are correct, I want to create a sql routine that will auto-increment/decrement the values based on the re-ordering.

regards,
Brian

http://www.gainesconsulting.com

The problem with doing something right the first time is that nobody appreciates how difficult it was.

BG12424 · Jan 5, 2005

Ok, I finally got it, but there's gotta be a simpler way than this. Can anyone give some pointers? Here is my stored procedure:

Code:

CREATE PROC sp__UpdateCMSPageType(
	@PageTypeId		int = 0,
	@PageType		varchar(50) = null,
	@TypeImage		varchar(50) = null,
	@Instructions		varchar(500) = null,
	@RowsPerColumn		int = 0,
	@PageOrder		int = 0
)
AS
BEGIN

	IF ( dbo.fn__TableExists('tblCMSPageType') > 0 )
	BEGIN

		IF ( 	@PageTypeId 		= 0 OR
			@PageType 		IS NULL OR
			@TypeImage 		IS NULL OR 
			@Instructions 		IS NULL OR
			@RowsPerColumn 		= 0 OR
			@PageOrder 		= 0 )
		BEGIN
			PRINT 'PageTypeId, PageType, TypeImage, Instructions, RowsPerColumn, and PageOrder are a required field.'
			RETURN 0
		END
		ELSE
		BEGIN
			BEGIN TRAN

			DECLARE @maxOrder int
			SELECT 	@maxOrder = MAX(PageOrder)
			FROM	tblCMSPageType

			DECLARE @currentOrder int
			SELECT 	@currentOrder = PageOrder
			FROM 	tblCMSPageType 
			WHERE 	CMSPageTypeId = @PageTypeId

			DECLARE @nextId int
			SELECT 	@nextId = CMSPageTypeId
			FROM 	tblCMSPageType 
			WHERE 	PageOrder = @PageOrder
			
			IF ( @currentOrder <> @PageOrder )
			BEGIN
				IF ( @PageOrder = 1 )
				BEGIN
					UPDATE 	tblCMSPageType
					SET	PageOrder = PageOrder + 1
					WHERE	PageOrder = @PageOrder			
				END
				ELSE
				BEGIN
					IF ( @PageOrder >= @maxOrder Or @PageOrder > @currentOrder ) 
					BEGIN						
						UPDATE 	tblCMSPageType
						SET	PageOrder = @PageOrder - 1
						WHERE	CMSPageTypeId = @nextId
					END
					ELSE
					BEGIN
						IF ( @PageOrder < @currentOrder )
						BEGIN
							UPDATE 	tblCMSPageType
							SET	PageOrder = PageOrder + 1
							WHERE	PageOrder = @PageOrder
						END
						ELSE
						BEGIN

							UPDATE 	tblCMSPageType
							SET	PageOrder = PageOrder + 1
							WHERE	PageOrder = @PageOrder
	
							UPDATE 	tblCMSPageType
							SET	PageOrder = @PageOrder
							WHERE	CMSPageTypeId = @nextId
						END
					END
				END			
			END

			UPDATE 	tblCMSPageType
			SET 	PageType 		= @PageType,
				TypeImage		= @TypeImage,
				Instructions		= @Instructions,
				RowsPerColumn		= @RowsPerColumn,
				PageOrder		= @PageOrder
			WHERE	CMSPageTypeId 		= @PageTypeId

			SELECT 	CMSPageTypeId, PageOrder 
			INTO 	#tempCMSPageType 
			FROM 	tblCMSPageType
			ORDER BY pageorder

			DECLARE @counter int
			SELECT 	@counter = 0
			UPDATE 	#tempCMSPageType 
			SET 	@counter = PageOrder = @counter + 1

			UPDATE tblCMSPageType
			SET PageOrder = 
				( 	SELECT 	PageOrder 
					FROM 	#tempCMSPageType 
					WHERE 	CMSPageTypeId = tblCMSPageType.CMSPageTypeId)

			DROP TABLE #tempCMSPageType


			IF ( @@ERROR = 0 )
			BEGIN
				COMMIT TRAN
				RETURN 1
			END
			ELSE
			BEGIN
				ROLLBACK TRAN
				RETURN 0
			END
		END
	END
	ELSE
	BEGIN
		PRINT 'A table which is required to perform this action is missing.'
		RETURN 0
	END

END

regards,
Brian

http://www.gainesconsulting.com

The problem with doing something right the first time is that nobody appreciates how difficult it was.

vongrunt · Jan 6, 2005

General idea:

- for specified ID find old order
- if new order is smaller than old one, swap them
- rotate only values between old and new order

There are many ways to do that. Here is one without IF statements (@PageID and @NewPageOrder are input arguments):

Code:

declare @PageId int; set @PageID = 3
declare @NewPageOrder int; set @NewPageOrder = 5

declare @OldPageOrder int
select @OldPageOrder = PageOrder from myTable where PageID=@PageID

update myTable
set PageOrder = PageOrder-sign(@NewPageOrder-@OldPageOrder)
where sign(PageOrder-@NewPageOrder)<> sign(PageOrder-@OldPageOrder)

update myTable set PageOrder=@NewPageOrder where PageID=@PageID

BG12424 · Jan 6, 2005

Thanks for posting. I've implemented your solution in my scenario and it appears that it works fine if say you want to make the current order from 3 to 4 or 4 to 3, but I noticed that when I wanted to change an order from say 5 to 1, then the original 1 order changed to 3.

regards,
Brian

http://www.gainesconsulting.com

The problem with doing something right the first time is that nobody appreciates how difficult it was.

BG12424 · Jan 6, 2005

Hold the phone vongrunt...my bad, I forgot to change my stored procedure name when testing.....It works as you explained..Nice job! Thanks

regards,
Brian

http://www.gainesconsulting.com

The problem with doing something right the first time is that nobody appreciates how difficult it was.

BG12424 · Jan 6, 2005

Ok, had a few corrections because it wasn't sorting properly. This now works great; here is the solution I went with in case anyone out there needs something like this:

Code:

ALTER PROC sp__UpdateCMSPageType(
	@PageTypeId		int = 0,
	@PageType		varchar(50) = null,
	@TypeImage		varchar(50) = null,
	@Instructions		varchar(500) = null,
	@RowsPerColumn		int = 0,
	@PageOrder		int = 0
)
AS
BEGIN

	IF ( dbo.fn__TableExists('tblCMSPageType') > 0 )
	BEGIN

		IF ( 	@PageTypeId 		= 0 OR
			@PageType 		IS NULL OR
			@TypeImage 		IS NULL OR 
			@Instructions 		IS NULL OR
			@RowsPerColumn 		= 0 OR
			@PageOrder 		= 0 )
		BEGIN
			PRINT 'PageTypeId, PageType, TypeImage, Instructions, RowsPerColumn, and PageOrder are a required field.'
			RETURN 0
		END
		ELSE
		BEGIN
			BEGIN TRAN
			DECLARE @maxOrder int
			SELECT 	@maxOrder = MAX(PageOrder)
			FROM	tblCMSPageType
			
			DECLARE @currentOrder int
			SELECT 	@currentOrder = PageOrder
			FROM 	tblCMSPageType 
			WHERE 	CMSPageTypeId = @PageTypeId
			
			DECLARE @nextId int
			SELECT 	@nextId = CMSPageTypeId
			FROM 	tblCMSPageType 
			WHERE 	PageOrder = @PageOrder
			
			IF ( @currentOrder <> @PageOrder )
			BEGIN
				IF ( @PageOrder = 1 )
				BEGIN			
					UPDATE 	tblCMSPageType
					SET	PageOrder = PageOrder + 1
					WHERE	PageOrder >= @PageOrder
			
					UPDATE 	tblCMSPageType
					SET	PageOrder = 2
					WHERE	PageOrder = 1
				END
				ELSE
				BEGIN
					IF ( @PageOrder >= @maxOrder Or @PageOrder > @currentOrder ) 
					BEGIN						
						UPDATE 	tblCMSPageType
						SET	PageOrder = @PageOrder - 1
						WHERE 	PageOrder > @currentOrder
						AND	PageOrder <= @PageOrder
					END
					ELSE
					BEGIN
						IF ( @PageOrder < @currentOrder )
						BEGIN
							UPDATE 	tblCMSPageType
							SET	PageOrder = PageOrder + 1
							WHERE	PageOrder >= @PageOrder
						END
						ELSE
						BEGIN
							UPDATE 	tblCMSPageType
							SET	PageOrder = PageOrder + 1
							WHERE	PageOrder = @PageOrder
			
							UPDATE 	tblCMSPageType
							SET	PageOrder = @PageOrder
							WHERE	CMSPageTypeId = @nextId	
			
						END
					END
				END			
			END

			UPDATE 	tblCMSPageType
			SET 	PageType 		= @PageType,
				TypeImage		= @TypeImage,
				Instructions		= @Instructions,
				RowsPerColumn		= @RowsPerColumn,
				PageOrder		= @PageOrder
			WHERE	CMSPageTypeId 		= @PageTypeId

			SELECT 	CMSPageTypeId, PageOrder 
			INTO 	#tempCMSPageType 
			FROM 	tblCMSPageType
			ORDER BY pageorder

			DECLARE @counter int
			SELECT 	@counter = 0
			UPDATE 	#tempCMSPageType 
			SET 	@counter = PageOrder = @counter + 1

			UPDATE tblCMSPageType
			SET PageOrder = 
				( 	SELECT 	PageOrder 
					FROM 	#tempCMSPageType 
					WHERE 	CMSPageTypeId = tblCMSPageType.CMSPageTypeId)

			DROP TABLE #tempCMSPageType

			IF ( @@ERROR = 0 )
			BEGIN
				COMMIT TRAN
				RETURN 1
			END
			ELSE
			BEGIN
				ROLLBACK TRAN
				RETURN 0
			END
		END
	END
	ELSE
	BEGIN
		PRINT 'A table which is required to perform this action is missing.'
		RETURN 0
	END

END

regards,
Brian

http://www.gainesconsulting.com

The problem with doing something right the first time is that nobody appreciates how difficult it was.

ESquared · Jan 6, 2005

Just do the update, and have this trigger:

Code:

CREATE TRIGGER MyTable_KeepOrdered ON MyTable FOR UPDATE
AS
SET NOCOUNT ON
IF UPDATE(PageOrder) BEGIN
   IF (SELECT Count(1) FROM Inserted I INNER JOIN Deleted D ON I.PageID = D.PageID WHERE I.PageOrder <> D.PageOrder) > 1 BEGIN
      RAISERROR ('Can only update one PageOrder at a time', 11, 1)
      ROLLBACK TRANSACTION
   END
   ELSE BEGIN
      UPDATE M
      SET M.PageOrder = M.PageOrder + CASE WHEN I.PageOrder < D.PageOrder THEN 1 ELSE -1 END
      FROM
         Inserted I
         INNER JOIN Deleted D ON I.PageID = D.PageID
         INNER JOIN MyTable M ON M.PageID <> I.PageID AND (M.PageOrder BETWEEN I.PageOrder AND D.PageOrder OR M.PageOrder BETWEEN D.PageOrder AND I.PageOrder)
      WHERE
         I.PageOrder <> D.PageOrder
   END
END

UPDATE MyTable SET PageOrder = 5 WHERE PageID = 3

It works great. You could also have insert and delete triggers, probably insert combined with update and delete separate.

Also, I could make it work if you needed to update more than one page order at a time, but you'd have to describe the exact behavior you wanted.

And it could also be modified to have many different PageOrders starting at 1, per article or something.

E

P.S. It's stupid that BETWEEN requires the lower value first and the higher value last!

-------------------------------------
• Every joy is beyond all others. The fruit we are eating is always the best fruit of all.
• It is waking that understands sleep and not sleep that understands waking. There is an ignorance of evil that comes from being young: there is a darker ignorance that comes from doing it, as men by sleeping lose the k

BG12424 · Jan 6, 2005

Thank you ESquared! I'll have to check it out.

regards,
Brian

http://www.gainesconsulting.com

The problem with doing something right the first time is that nobody appreciates how difficult it was.

BG12424 · Jan 7, 2005

Unfortunately, the SQL database is on a shared server, so the provider does not allow triggers.

regards,
Brian

http://www.gainesconsulting.com

The problem with doing something right the first time is that nobody appreciates how difficult it was.

r937 · Jan 7, 2005

you may want to shortcut a lot of that logic

i mean, ultimately you might need it, but in the interim, you can get up and running with this very simple strategy -- assign your sort sequence numbers in increments of 100

Code:

PageId PageOrder
------ ----------
1      100
2      200
3      300
4      400
5      500
6      600
7      700

then in order to resequence 3 to go between 5 and 6, you simply issue this:

Code:

update yourtable 
   set PageID=550
 where PageID=300

you can keep doing this indefinitely, until such time as you've totally reshuffled hundreds of entries, at which point you still don't need all that fancy application logic, you simply need to resequence all the numbers by 100 again (an identity column, even on a temporary basis, can do this)

i don't want to use the phrase "over-engineer a solution" so i won't ;-)

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

donutman · Jan 7, 2005

Spoilsport. [rofl2]

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

vongrunt · Jan 7, 2005

It depends on how fancy is application logic. Asking user to enter "tab index" value is one thing, drag&drop with VeryKewlTreeControl another.

ESquared · Jan 7, 2005

What if 550 is already used? It's not so simple as all that. I thought of a slightly different method, myself, but was interested in answering the question as posed. Have a "dateupdated" value. Then just sort by pageorder, dateupdated desc. But wait, that doesn't work. Aha! You could make pageorder a text field. Any time you want to move a page in the order, just take the value of the page you want it directly after and add a 5 to it:

Code:

1   1    1
2   2--| 4
3-| 4  | 5
4 | 5  | 55
5 |-55 |_555
6   6    6
7   7    7

Then just order it with the values left justified. Or use decimals if that makes it clearer:

Code:

.1
.4
.5
.55
.555
.6
.7

r937 · Jan 7, 2005

ESquared, you are evil, and you're my hero

nice job

very creative thinking

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

donutman · Jan 7, 2005

And don't you just love the way he draws those arrow diagrams...well that one is nothing. You should see the ones he did to discuss the defination of polygons.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

ESquared · Jan 7, 2005

And I used underscore instead of dash for the second line in my little diagram. [sad]

vongrunt · Jan 7, 2005

Questions:

- what will happen if soneone "moves" another row to .55? Without extra logic to handle it.
- what will happen when .55555... runs out of scale?

Btw. I like idea w/ decimals too.

donutman · Jan 7, 2005

I'm working on finding a faster way to do the row by row update...and without a cursor, hehe. So, that you could reorder them every once in a while to avoid the scale issue.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Increment/Decrement Help 2

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Technical User

Programmer

Programmer

Programmer

Technical User

Programmer

Programmer

Programmer

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor