SQL Mass Reordering of rows

Question

I have a table with a list of records, and a column called order. I have an AJAX script to drag and drop the table rows up or down which I want to use to perform a query, reordering the rows as they have been dragged.

In the PHP, I perform a query to get the current order of the records. eg 1, 2, 3 ,4 The AJAX function passes the new order after the drag/drop is complete, eg 3, 1, 2, 4

Is there a simple way to re-order the records in one go, based on the new values? The only other alternative I can see is looping through UPDATE statements eg SET order = 1 where order = 3

But surely this would result in 2 records having the same value?

Apologies, I know this description may be slightly confusing.

Answer 1

Order should not be your primary key; do the updates by using the primary key in the where clause.

You can do it all in one query using a fairly long CASE statement, if you really want to. Example:

UPDATE foo
  SET order = CASE order
    WHEN 1 THEN 2
    WHEN 2 THEN 3
    WHEN 3 THEN 4
    WHEN 4 THEN 5
  END
WHERE order IN (1,2,3,4)

(Remember that SQL statements behave as if they change all values simultaneously, so that will not do something like change 1 to 2, then to 3, etc.)

Answer 2

If you know the new row position you can do this:

CREATE PROCEDURE [proc_UpdateCountryRowOrder]
    @ID UNIQUEIDENTIFIER,
    @NewPosition INT
AS

SET NOCOUNT ON

DECLARE @CurrentPosition INT
DECLARE @MaximumPosition INT

IF (@NewPosition < 1) SET @NewPosition = 1

SELECT @CurrentPosition = [Countries].[Order]
FROM [Countries]
WHERE [Countries].[ID] = @ID

SELECT @MaximumPosition = MAX([Countries].[Order])
FROM [Countries]

IF (@NewPosition > @MaximumPosition) SET @NewPosition = @MaximumPosition

IF (@NewPosition <> @CurrentPosition)
BEGIN
    IF (@NewPosition < @CurrentPosition)
    BEGIN
        BEGIN TRAN

        UPDATE [Countries]
        SET [Countries].[Order] = [Countries].[Order] + 1
        WHERE [Countries].[Order] >= @NewPosition
        AND [Countries].[Order] < @CurrentPosition

        UPDATE [Countries]
        SET [Countries].[Order] = @NewPosition
        WHERE ID = @ID

        COMMIT TRAN
    END
    ELSE
    BEGIN
        BEGIN TRAN

        UPDATE [Countries]
        SET [Countries].[Order] = [Countries].[Order] - 1
        WHERE [Countries].[Order] <= @NewPosition
        AND [Countries].[Order] > @CurrentPosition

        UPDATE [Countries]
        SET [Countries].[Order] = @NewPosition
        WHERE ID = @ID

        COMMIT TRAN
    END
END
GO

Answer 3

You really want to go back and look at the ajax function results. See if you can get a list of reordered elements instead of just the new list order.

You could write a function (in C#/VB) to determine this for you in code given the before and after lists. Once you have the delta changes, you could issue an update transaction with one update command per reorder.

If you want to work with just the new order. Try adding a new column called neworder and update this. Then bulk update currentcolumn=newcolumn once done looping.