UPDATE field on ORDER of another field-CodePudding

If for example, the table schema is below

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

Sample Data:

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

Need to update Sequence field to:

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

How to do this? What is the query? I prefer not to use CURSOR. So what I trying to achieve is set Sequence field based on the Alphabetic order of Name field.

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;