CODEWITHSUNDEEP
sql-servert-sqlhierarchy
Kardo
Kardo

Reputation: 1708

What is the best way to move some items in a hierarchy table up or down?

Imagine I want to reorder (change the location or order) of an item in a Menu by replacing its order with the next or previous item. To do this I use such a code as follow:

    DECLARE @SO tinyint --, @MenuID uniqueidentifier
    --SET @MenuID = (Select top 1 MenuID from MenuItems where MenuItemID = @ID)
    SET @SO = (Select top 1 SO from MenuItems where MenuItemID = @ID and MenuID = @MenuID)

    if @SO > 1
    begin
        UPDATE  dbo.MenuItems SET SO = @SO WHERE MenuItemID = (select top 1 MenuItemID from MenuItems WHERE SO = @SO - 1 and MenuID = @MenuID)
        UPDATE  dbo.MenuItems SET SO = @SO - 1 WHERE MenuItemID = @ID
    END

This code works fine for one move up or down each time. I have two questions:

  1. Is there a better solution to do this?
  2. What if I want to move some items at the same time to some steps higher or lower, like this:?

From:

> 1
> 2
    > 1
    > 2
    > 3
> 3
> 4
    > a
    > b
> 5

To:

> 1
> 2
    > 1
    > 2
    ? a
    ? 5
    > 3
> 3
> 4
    > b

Thanks, Kardo

Upvotes: 0

Views: 157

Answers (1)

HABO
HABO

Reputation: 15816

You can swap the order of two arbitrary entries using a single query like this:

update dbo.MenuItems
  set SO = case
    when MenuItemId = @FirstId then ( select SO from dbo.MenuItems where MenuItemId = @SecondId )
    else ( select SO from dbo.MenuItems where MenuItemId = @FirstId ) end
  where MenuItemId in ( @Firstid, @SecondId )

The lookups for the id's can be performed prior to the query or embedded in it.

Since you didn't include your table schema in the question the query may need a little tweaking. I assumed that MenuItemId is unique, but that certainly isn't clear from your post.

Upvotes: 1

Related Questions