SQL to move rows up or down in two-table arrangement

Question

I have two tables that I designed this way with a possible reshuffling of elements in mind:

1. [dbo.test_db_002] with columns:
[id] = INT NOT NULL IDENTITY(1,1) PRIMARY KEY
[name] = NVARCHAR(255)

and

2. [dbo.test_db_003] with columns:
[ord] = INT
[itmid] = INT NOT NULL PRIMARY KEY

[itmid] column has a constraint linking it to [dbo.test_db_002].[id] like so:

ALTER TABLE [dbo.test_db_003] 
ADD CONSTRAINT fk1 FOREIGN KEY ([itmid]) 
REFERENCES [dbo.test_db_002]([id]) 
ON DELETE CASCADE ON UPDATE CASCADE;

Say, [dbo.test_db_002] table has the following data:

[id] [name] 
3    John
5    Mary
8    Michael
10   Steve
13   Jack
20   Pete

and [dbo.test_db_003] has the following ordering data:

[ord] [itmid]
1      5
4      8
5      13
8      3
10     10
13     20

So when I retrieve names from the database I use the following SQL:

SELECT [name]
FROM   [dbo.test_db_002] t1
LEFT JOIN [dbo.test_db_003] t2 ON t1.[id]=t2.[itmid]
ORDER BY t2.[ord] ASC

It produces the list of names (ordered by the [dbo.test_db_003].[ord] column):

Mary
Michael
Jack
John
Steve
Pete

What I am looking for is an option to move each of the names up and down the list. For instance, if I want to move "John" one position up, what do I do?

So far I came up with this partial SQL:

WITH cte AS
(
    SELECT [id], [ord], ROW_NUMBER() OVER (ORDER BY t2.[ord] ASC) AS rowNum
    FROM [dbo.test_db_002] t1
    LEFT JOIN [dbo.test_db_003] t2 ON t1.[id] = t2.[itmid]
)

That will select the following:

rowNum  [id]  [ord]
1        1     5
2        4     8
3        5     13
4        8     3
5        10    10
6        13    20

So I understand that I need to shift values in [ord] column up by one starting from the index 3 (since "John" index is 4) and then somehow make "John"'s [ord] to be set to 5, but how do you do that?

Answer 1

I prepared a complete demo for you how this can work on data.stackexchange.com.
The solution is tailored to your comment:

the move up or down can be only a single step - in other words, one cannot move 2 or more positions

In the example I make John trade ordinal positions with Jack above him:

WITH x AS (
  SELECT t2.itmid, t2.ord
  FROM   dbo.test_db_002 t1
  LEFT   JOIN dbo.test_db_003 t2 ON (t1.id = t2.itmid)
  WHERE  t1.name = 'John'  -- must be unique, or query by id ...
  )
  , y AS (
  SELECT TOP 1
         t.itmid, t.ord
  FROM   dbo.test_db_003 t, x
  WHERE  t.ord < x.ord     -- smaller ord = "above"
  ORDER  BY t.ord DESC
  )
UPDATE dbo.test_db_003 SET ord = z.ord
FROM (
   SELECT x.itmid, y.ord FROM x,y
   UNION ALL
   SELECT y.itmid, x.ord FROM x,y
   ) z
WHERE  dbo.test_db_003.itmid = z.itmid   

###Major points:

0. Use two CTE to structure the query:
1. Get John's id & ordinal position
2. Get the same for the person above him
3. Prepare two rows where these two switch ordinal numbers with the help of UNION ALL
4. Use these two rows in a now simple UPDATE

• The ordinal position ord must allow passing duplicates for this to work.
• If there is nobody 'above', the query will silently do nothing.