Moving and re-ordering records permanently

Question

I have a table Items e.g.

ID  Name     ItemOrder
----------------------
16  Albania  1
13  Chile    2
11  Barbados 3 
10  France   4
...

Which I display in my client application as

SELECT * FROM Items ORDER BY ItemOrder

I want to be able to "move" for example Albania and Barbados After (or Before depending on my parameter) France or any other record. so the result is (for move After record France):

13  Chile    1    
10  France   2 
16  Albania  3 
11  Barbados 4 

And result for Before is:

13  Chile    1        
16  Albania  2 
11  Barbados 3 
10  France   4 

I need to update permanently the ItemOrder to reflect the new order. Is there a simple and efficient way to do this?

Answer 1

You can use a common table expression (CTE) to perform the UPDATE operation. This is how your query could look like for the 'After' case:

;WITH ToUpdate AS (
  SELECT ID, Name, ItemOrder,
         ROW_NUMBER() OVER (ORDER BY temp_order) AS NewOrder
  FROM (
  SELECT ID, Name, ItemOrder,
         CASE 
            WHEN Name IN ('Albania', 'Barbados') THEN x.o + ItemOrder * 0.1 
          ELSE ItemOrder
         END AS temp_order
  FROM mytable 
  CROSS JOIN (SELECT ItemOrder FROM mytable WHERE Name = 'France') AS x(o)) AS t
)
UPDATE ToUpdate
SET ItemOrder = NewOrder

The trick is to get France's order number and add a fraction of the order of 'Albania', 'Barbados', so that:

• 'Albania', 'Barbados' are positioned after 'France'
• 'Albania' and 'Barbados' maintain the order between them
• The order of the records following 'France' is also maintained.

Demo here

Edit:

The 'Before' case can be implemented if we substitute this line:

WHEN Name IN ('Albania', 'Barbados') THEN x.o + ItemOrder * 0.1 

with this line:

WHEN Name IN ('Albania', 'Barbados') THEN x.o - 1.0/ItemOrder * 0.1  

Answer 2

(Note: This answer was written before you updated your question to mention that you need to update the ItemOrder.)

First, make some space between your entries:

SELECT ID, name, ItemOrder * 10 AS newOrder FROM Items

Now we modify that SQL to put the "special ones" in between:

SELECT ID, name, ItemOrder
       CASE WHEN name = 'Albania'
            THEN (SELECT ItemOrder FROM Items WHERE name = 'France') * 10 + 1
            WHEN name = 'Barbados'
            THEN (SELECT ItemOrder FROM Items WHERE name = 'France') * 10 + 2
            ELSE ItemOrder * 10
        END AS newOrder
  FROM Items

And now we can sort by it:

SELECT ID, name, ItemOrder
  FROM (... see previous SQL ...) AS t
 ORDER BY newOrder