How to update table records in reverse order?

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.)

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;

StackExchange Database Administrators Q#317562, answer score: 4