SqlServer中增加一列递增的序号

UPDATE [2DCode].[dbo].[tb2DCodeInfo] 
   SET [CodeID] =B.autoId from (select *, ROW_NUMBER() OVER (order by CodeToComponents asc) as autoId  from [tb2DCodeInfo]) B 
where [tb2DCodeInfo].CodeToComponents=B.CodeToComponents

首先使用select *, ROW_NUMBER() OVER (order by CodeToComponents asc) as autoId from [tb2DCodeInfo]语句获取表tb2DCodeInfo中的信息和行号，然后使用更新语句更新原表中的CodeID字段。注意不要丢掉where条件，否则，值不会更新