SQL Server: change order column in DB

In my Products table I have a column called 'order' which contains integers 1, 2, 3 etc which are set by a drop down box by the user in the CMS.

I want to be able to re order all rows in the DB by passing the old开发者_C百科 order, new order and product ID accordingly when the user changes the order of a certain product.

Eg:

Chair - order 1
Pillow - order 2
Bed - order 3
Couch - order 4

If the use changes Couch to be order 2, I need to reset all other products according to this change.

Optimisation is not a huge concern as there wont be many items in the table.

What is the most efficient logic for this task?

Thanks

Your order column does not need to contain consecutive integers - there can be gaps. The only thing that is important is their relative order. You can start with 100, 200, 300, ... instead of 1, 2, 3, ... making it easier to adjust the relative ordering without having to update all items in the database just because you want to move one item.

100 foo ---      200 bar
200 bar    \     300 baz
300 baz     ---> 350 foo
400 qux          400 qux

Occasionally you will find that there are no gap left at the insertion point. Then you can renumber the entire table.

CREATE PROCEDURE ChangeProductOrder(@productID int, @order int)
AS

DECLARE @oldOrder int
SET @oldOrder = (SELECT [Order] FROM dbo.Products WHERE productid = @productID)

UPDATE p
SET [order] = CASE WHEN ProductID = @productID 
                 THEN @order 
                 ELSE [Order] - SIGN(@order - @oldOrder) 
              END
FROM dbo.Products p
WHERE ([Order] BETWEEN @oldOrder AND @order
OR [Order] BETWEEN @order AND @oldOrder)

How about something like this (Pseudo code)

UPDATE Table
SET     Order = Order + 1
WHERe   Order >= NewOrderForCouch 
AND     Order < CurrentOrderForCouch;
UPDATE Table
SET     Order = NewOrderForCouch
WHERe   ID = CouchID;

To do this, your table needs to have a unique identifier, to be able to differentiate between

Couch 2

and

Pillow 2

Create your Orders table like this

CREATE TABLE [dbo].[Orders](
    [ID] [int] NOT NULL,
    [Item] [varchar](50) NOT NULL,
    [Number] [int] NOT NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

Once populated, you have this (notice how I've put random unique idenitifiers in the unique key ID):

ID      Item    Number
593 Chair   1
207 Pillow  2
681 Bed     3
117 Couch   4

You change the number of Couch to 2:

ID      Item    Number
593 Chair   1
207 Pillow  2
681 Bed     3
117 Couch   2

We can use ID to differentiate Couch and Pillow that both have number 2. The ROW_NUMBER() function gives us the desired numbering:

SELECT
    id, Item, number, ROW_NUMBER() OVER(ORDER BY number)
FROM
    Orders

giving

593 Chair   1   1
117 Couch   2   2
207 Pillow  2   3
681 Bed     3   4

It's then just a matter of updating with these new numbers, using a WITH clause:

WITH NewOrders(newnum, id) AS (
    SELECT
        ROW_NUMBER() OVER(ORDER BY number), id
    FROM
        Orders
)
UPDATE
    Orders
SET
    number = (
        SELECT
            newnum
        FROM
            NewOrders
        WHERE
            neworders.id = orders.id    
    )

Results:

ID  Item    Number
593 Chair   1
117 Couch   2
207 Pillow  3
681 Bed     4