急！！！！如何批量的给表内记录中某个字段递增编号
如表
id name
----------
2 ee
2 rr
7 xx
9 ss
17 ww
现在要变成
id name
----------
1 ee
2 rr
3 xx
4 ss
5 ww
请问这个update该怎么写啊？
[解决办法]
create table t(
id int,
name varchar(10)
)
insert into t
select 2, 'ee '
union all select 2, 'rr '
union all select 7, 'xx '
union all select 9, 'ss '
union all select 17, 'ww '
select identity(int,1,1) as [id],[name] into #t from t
select * from #t
update t set id=a.id from #t a where a.name=t.name
select * from t
drop table #t
drop table t
[解决办法]
alter table t1 with nocheck add sn int identity(1,1);
update t1 set id=sn;
alter table t1 drop column sn;
[解决办法]
update tab set tab.id = tt.id2
from
(
select id2 = (select count(1) from tab where id < a.id or (id = a.id and name = a.name)),id,name
from tab a
)tt
where tab.id = tt.id and tab.name = tt.name
