Sample Header Ad - 728x90

How to update table records in reverse order?

4 votes
5 answers
5603 views
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.*)
Asked by TAHER El Mehdi (292 rep)
Sep 28, 2022, 08:17 PM
Last activity: Oct 20, 2022, 02:04 AM