修改记录的编号为重复记录的的编号的较小者，怎么用sql语言实现

修改记录的编号为重复记录的的编号的较小者，如何用sql语言实现？
假设我表里有记录：
Aid AName Aoher
1 lily 2233
2 lucy 568
3 jack 589
4 lily 2233
5 david 25
6 lily 2233
7 lucy 568
8 lucy 568
9 jack 589
......
其中里面有重复的记录（除了aid不同外）
我要更新表使它变成：
Aid AName Aother
1 lily 2233
2 lucy 568
3 jack 589
1 lily 2233
5 david 25
1 lily 2233
2 lucy 568
2 lucy 568
3 jack 589
说明：即修改记录的编号为重复记录的的编号的较小者，如何用sql语言实现？

[解决办法]
aid如果不要求唯一

SQL code

update a set   aid = (select min(aid) from tab where aname = a.aname and Aoher = a.Aoher)from tab a
[解决办法]
update A
set A.Aid = B.Aid
from TableA A,
(
  select min(Aid),AName,Aoher
  from TableA
  group by AName,Aoher
  ) B
where B.AName = A.AName and B.Aoher = A.Aoher


[解决办法]
SQL codecreate table vic(Aid int, AName varchar(6), Aoher int)insert into vicselect 1, 'lily', 2233 union allselect 2, 'lucy', 568 union allselect 3, 'jack', 589 union allselect 4, 'lily', 2233 union allselect 5, 'david', 25 union allselect 6, 'lily', 2233 union allselect 7, 'lucy', 568 union allselect 8, 'lucy', 568 union allselect 9, 'jack', 589with t as(select row_number() over(partition by AName,Aoher order by Aid) rn,Aid,AName,Aoher from vic)update a set a.Aid=b.Aidfrom vic ainner join (select * from t where rn=1) b on a.AName=b.AName and a.Aoher=b.Aoherselect * from vicAid         AName  Aoher----------- ------ -----------1           lily   22332           lucy   5683           jack   5891           lily   22335           david  251           lily   22332           lucy   5682           lucy   5683           jack   589(9 row(s) affected)

修改记录的编号为重复记录的的编号的较

热点推荐