CODEWITHSUNDEEP
sqlsql-servert-sql
tickwave
tickwave

Reputation: 3455

How to reorder numbering in INT column in SQL?

Let's say I have a table Customer

name                 seq
-------------------- -----------
Johnny               1
Jack                 3
Jim                  4

Both name and seq are primary key. This is my expected result

name                 seq
-------------------- -----------
Johnny               1
Jack                 2
Jim                  3

Any idea how to achieve this? I tried using UPDATE along with ROW_NUMBER but it won't let me due to num is a primary key column.

Note: The new sequence must be ordered by seq (not name).

Upvotes: 0

Views: 395

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81990

You can update via a CTE

Declare @YourTable table (name varchar(25),seq int)
Insert Into @YourTable values
('Johnny',1),
('Jack',  3),
('Jim',   4)

;with cte as (
    Select *
          ,RN = Row_Number() over (Order By Seq)
    From  @YourTable
)
Update cte Set Seq=RN

Select * from @YourTable

Returns

name    seq
Johnny  1
Jack    2
Jim     3

Upvotes: 2

Related Questions