delete top (n) excluding the first row from table with no primary key

[Pauke]

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)

I'm looking for a solution that will not involve using creating additional identity column or copying to a table with this primary key set because this table may have huge amount of rows, and these options are too time consuming.

[Delerna]

As this is a one off cleanup operation I suggest you resort to a cursor.


One thing concerns me,
you say the rest of the fields might be identical or they might not be.

This suggests to me the possibility of there being 2 or more records for a particular ID and that all of those records are completely identical.
If this is true then you have no way of identifying 1 of them over the others.
Either you will have to delete all of them and then re-insert 1 back, or you will have to add an identity field.

[Delerna]

Oh, by the way, I hope your going to take the time to make a backup copy of the table before you do this?

[Pauke]

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?