UPDATE column on ORDER of another column-CodePudding

If for example, the table schema is below

CREATE TABLE MyHits  
( 
    Name varchar(20) NOT NULL,  
    Sequence int NULL
);

Sample data:

Name Sequence
---- --------
Dan  
Ana
Len

I need to update Sequence column to:

Name Sequence 
---- --------
Dan  2 
Ana  1 
Len  3

How to do this? What is the query?

I prefer not to use a cursor. What I trying to achieve is set Sequence column based on the alphabetic order of Name column.

CodePudding user response：

You can use row_number here and an updatable CTE:

with t as (
    select *, Row_Number() over(order by name) rn
    from myhits
)
update t set Sequence = rn;