CODEWITHSUNDEEP
sqlsql-servert-sql
Beweelam
Beweelam

Reputation: 1126

reorder a field of a table

I have this table named Employee : 

IdEmploye    |     Order
    10                1 
    17                5 
    102               6 
    66                77

I want to re-order this table into this :

IdEmploye    |     Order
    10                1 
    17                2 
    102               3 
    66                4

How it's possible in SQL ?

Thanks !

Upvotes: 0

Views: 56

Answers (3)

Eric Brandt
Eric Brandt

Reputation: 8101

SQL Server supports the use of ROW_NUMBER(), so this will get you there.

UPDATE tbl
SET [Order] = sub.NewOrder
from tbl
JOIN
(select IdEmploye, ROW_NUMBER() OVER (ORDER BY IdEmploye) as NewOrder
 from tbl) as sub
 ON sub.IdEmploye = tbl.IdEmploye;

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use CTE :

with empupdt as (
     select e.*, row_number() over (order by IdEmployee) as seq
     from employee e
)
update empupdt 
     set Order = seq;

Upvotes: 1

zip
zip

Reputation: 4061

You can do a straight update

Update tbl
Set Order = IdEmploye

Upvotes: 0

Related Questions