How to update table records in reverse order?

Question

I've a table Student

Id  Name    Mark
1   Medi    10
2   Ibra    15
3   Simo    20

and I want to update it, where I want to reverse it in descending order only Name and Mark and keep Id in its order:

Id  Name    Mark
1   Simo    20
2   Ibra    15
3   Medi    10

So Firstly, I reverse the order from top to bottom with row_number():

SELECT row_number() OVER (ORDER BY  [Id]) [Id],[Name],[Mark] 
FROM [Student] 
ORDER BY [Id] DESC

But What I need is to update not just select.
So Secondly I tried to update those two columns.

UPDATE students_ordered
set students_ordered.[Name]=students_reversed.[Name],
    students_ordered.[Mark] =students_reversed.[Mark]
from (SELECT row_number() OVER (ORDER BY  [Id]) [Id],[Name],[Mark]
  FROM [test].[dbo].[Student] students_ordered) students_ordered
inner join 
(SELECT row_number() OVER (ORDER BY  [Id]) [Id],[Name],[Mark] 
FROM [Student] 
ORDER BY [Id] DESC) students_reversed
  on students_reversed.Id=students_ordered.Id

I got an error :/ "Msg 1033, Level 15, State 1: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified."

I didn't lose hope and Tried to update only one field, then pass to the other But query goes in vain:

UPDATE Student Student_set
set Student_set.Name = (SELECT [Name]
FROM [Student] Student_get
where Student_set.id=Student_get.id ORDER BY row_number() OVER (ORDER BY [Id]) DESC)

Since the id I can't be update it(Id is a primary key and it is incremented by one.)

Answer 1

You were on the right track with row_number but had the wrong sorting.

We first build and populate the table:

create table #tmp(Id int, name varchar(4), mark int)

insert into #tmp
values (1,'Medi', 10),
(2, 'Ibra', 15),
(3, 'Simo', 20)

Then use row_number and order by mark desc, which gets you the row number in the order you require:

SELECT *,
       ROW_NUMBER() OVER(
       ORDER BY mark DESC) AS rn
FROM #tmp

Now we can update the original table using the row_number as the key to update:

 WITH cte
     AS (SELECT *,
                ROW_NUMBER() OVER(
                ORDER BY mark DESC) AS rn
         FROM #tmp)

     UPDATE a
       SET mark = b.mark,
           name = b.name
     FROM #tmp a
     INNER JOIN cte b
          ON a.Id = b.rn;

Answer 2

You don't need a self-join here, you can update the CTE directly.

You also don't need to swap all the other columns, just swap the ID.

create table #tmp (Id int, name varchar(4), mark int);

insert into #tmp values
(1   ,'Medi',    10),
(2   ,'Ibra',    15),
(3   ,'Simo',    20);

WITH cte AS (
    SELECT *,
      rn = ROW_NUMBER() OVER (ORDER BY ID DESC)
    FROM #tmp
)
UPDATE cte
SET ID = rn;

db<>fiddle

Answer 3

Another method - without self join.

Tested in dbfiddle.uk:

WITH cte AS (
  SELECT *,
    1 + COUNT(1) OVER () 
          - 2 * ROW_NUMBER () OVER (ORDER BY ID)
    AS rn      -- This is not a row_number.
               -- Say that the total number of rows is 7.
               -- That number is calculated by COUNT(1) OVER ().
               -- Then for any row, say the one with row_number = 2
               -- (when we order by id ASC)
               -- we want to exchange its values (name, mark)
               -- with the row that has row_number = 2
               -- (when we order by id DESC).
               -- That would be row_number = 6 (when order by id ASC).
               -- So, how to get from 2 to 6? The difference is 6-2 = 4.
               -- The two rows (2 and 6 in this case) have a sum of 8.
               -- Always. 1+7 = 2+6 = 3+5 = 4+4 = ... = 7+1 = 8
               -- So to find that 6, we need: 8-2 = Total + 1 - Row_number
               -- And to find that wanted 4, we need:
               -- (6-2) = (Total + 1 - Row_number) - Row_number
               -- Finally that is the same as:
               -- Total + 1 - 2 * Row_number
  FROM tmp
),
  
cte2 AS (
  SELECT *,
    -- we cannot use negative step with LEAD() or LAG()
    -- so we use the absolute value
    CASE WHEN rn > 0
      THEN LEAD(name, ABS(rn)) OVER (ORDER BY id)
      ELSE  LAG(name, ABS(rn)) OVER (ORDER BY id)
    END AS name_r,
    CASE WHEN rn > 0
      THEN LEAD(mark, ABS(rn)) OVER (ORDER BY id)
      ELSE  LAG(mark, ABS(rn)) OVER (ORDER BY id)
    END AS  mark_r
  FROM cte
)
UPDATE cte2
SET name = name_r,
    mark = mark_r ;

Answer 4

Based on what you have described, my answer is: "don't do it".

One of the basic premises of Relational databases is that the data can be stored unordered. You can always order the results when you select them. Their rank or order should be independent of their Id.

What happens if a student's mark or grade changes or is altered? Are you going to rewrite the whole table each time? That would be inefficient and a nightmare at large scale.

Edit: What happens when you get 2 students with the same Score and Name?

Answer 5

In SQL Server (and maybe other products) you could create a non-clustered primary key on ID, and a clustered index on the Name & Score which would affect the sort order when stored/selected. (But I still wouldn't rely on it)

Since the Id on the table has no value as a persistent ID you could just ignore it. In fact since it's volatile and serves no purpose you might as well drop it.