Thanks for your reply
I was given a solution on another place
with c as
(
select *, row_number() over(partition by id order by (select 0)) as n
from t1
)
delete from c
where n > 1;
However, I also need a solution for MsSQL 2000. Any ideas?
Pauke
Last Activity: Apr 16 '08, 07:29 AM
Joined: Apr 13 '08
Location:
-
delete top (n) excluding the first row from table with no primary key
I want to add a primary key to a table with some duplicates and I have to get rid of them first, keeping the first row from each duplication case.
Say I found a case of duplication where there are 5 rows with t1.id=3. Other columns may be identical or not (I cannot count on it).
I'm looking for something like
delete top (5) * from t1 where not exists (select top (1) * from t1 where t1.id=3)
...
No activity results to display
Leave a comment: