请教一条更新语句
请教一条更新语句


表a
    id       num
100         10
101         10
102         10
103         20
104         20
105         30
106         30
107         30
108         40        
如何写更新语句得到下面的结果
100         100
101         100
102         100
103         103
104         103
105         105
106         105    
107         105
108         108

谢谢各位

------解决方案--------------------
update tablename set num = (select min(a.id) from tablename a where a.num = num)
------解决方案--------------------
create table T(id int, num int)
insert T select 100, 10
union all select 101, 10
union all select 102, 10
union all select 103, 20
union all select 104, 20
union all select 105, 30
union all select 106, 30
union all select 107, 30
union all select 108, 40

update T set num=B.id
from(
select id=min(id), num from T group by num) B
where T.num=B.num

select * from T
--result
id num
----------- -----------
100 100
101 100
102 100
103 103
104 103
105 105
106 105
107 105
108 108

(9 row(s) affected)