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


表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)