update tableA set tablecode= b.a from (select (‘XXXXXXX‘||right(cast(power(10,4)+ row_number() over(order by createdon) as varchar),4)) as a,* from tableA ) as b where tableA.tableAid= b.tableAid
tableA ：表名
tablecode：需要自动编号的字段
tableAid : 主键id
XXXXXX:自定义的前缀
原文：https://www.cnblogs.com/issac-9608/p/14543410.html