Set NOCOUNT on;

IF OBJECT_ID('dbo.T1','U') is not null drop table dbo.T1

Go

Create table dbo.T1
(
col1 int null,
col2 VARCHAR(10) not null
);

Insert into dbo.T1(col2)
Values('C'),('A'),('B'),('A'),('C'),('B');



WITH C AS 
(
Select col1,col2, ROW_NUMBER() over(order by col2) AS rownum
 From T1
)


UPDATE C 
Set col1 = rownum

UPDATE C 
   Set col1 = rownum
   FROM(Select col1,col2, ROW_NUMBER() over(order by col2) AS rownum
            From T1
       ) C