sql server 2008 - T-SQL - How to update these records -

myfield     description -------     ----------- a001        alcantara a002        apacible             arctic             arkansas  b001        backdoor             ballpen             brother         ..and on 

on records above, how query (in 1 query possible) in t-sql update having output this:

myfield     description -------     ----------- a001        alcantara a002        apacible a003        arctic a004        arkansas  b001        backdoor b002        ballpen b003        brother 

thanks in advance!

try this:

;with cte ( select myfield,description,row_number() on (partition left([description],1) order [description]) rn table1 )  update cte set myfield=left(description,1) +case when rn <= 9 '00' else '0' end+cast(rn varchar(3)) cte