CODEWITHSUNDEEP
sqlsql-server
Miguel Terlaak
Miguel Terlaak

Reputation: 175

One query to update a table in a certain order

Let's say I have to following table:

id      LISTORDER   ARTIKEL_NR
209321  1           F99-           
219268  2           F99-0013663    
209323  3           FLB-0000035    
209324  4           FLB-0000142    
209325  5           INK-0000012    
209322  6           FLB-0000019    
209326  7           INK-0000003    
209327  8           ENE-1          
209328  9           ENE-2          
209329  10          CON-0000028

Now I want to change the LISTORDER column to the numbers from the id column so that the result will be:

id      LISTORDER   ARTIKEL_NR
209321  209321      F99-           
219268  209322      F99-0013663    
209323  209323      FLB-0000035    
209324  209324      FLB-0000142    
209325  209325      INK-0000012    
209322  209326      FLB-0000019    
209326  209327      INK-0000003    
209327  209328      ENE-1          
209328  209329      ENE-2          
209329  219268      CON-0000028

So the order stays the same but I use the original id numbers in another order. Is this possible in one query? Otherwise I have to save the result from the query ordered by id and then update for each row the listorder but ordered by listorder.

Something like:

UPDATE table SET listorder = (select id from table order by listorder)

Upvotes: 0

Views: 52

Answers (1)

Prashant SINGH SENGAR
Prashant SINGH SENGAR

Reputation: 137

I think below query should work for you.

;with cte as(
      Select RN=ROW_NUMBER() over(order by id),id from #YourTable
)
update t set t.LISTORDER=c.id 
from cte c join #YourTable t on c.RN=t.LISTORDER

Upvotes: 1

Related Questions