关于重复记录的的更改,该怎么解决

2012-01-03

关于重复记录的的更改有表 T 结构如下:idnameqty1a102b203c404a10现在想更改如下:idnameqty1a102b203c404a

关于重复记录的的更改
有表 T 结构如下:
id name qty
1 a 10
2 b 20
3 c 40
4 a 10
现在想更改如下:
id name qty
1 a 10
2 b 20
3 c 40
4 a 0
请高手指教!最好能附上代码,谢

[解决办法]

SQL code

-- tryupdate yourTable   set qty = 0 where id in (select id                from (select t.*,                             row_number() over(partition by name, qty order by id) rn                        from yourTable t)               where rn <> 1)
[解决办法]
SQL code-- TRY IT ..UPDATE TT T1   SET QTY = 0 WHERE EXISTS (SELECT 1          FROM TT T2         WHERE T1.NAME = T2.NAME           AND T1.ID > T2.ID);