By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,379 Members | 1,377 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,379 IT Pros & Developers. It's quick & easy.

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

P: 2
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.
Apr 13 '08 #1
Share this Question
Share on Google+
3 Replies


Delerna
Expert 100+
P: 1,134
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.
Apr 13 '08 #2

Delerna
Expert 100+
P: 1,134
Oh, by the way, I hope your going to take the time to make a backup copy of the table before you do this?
Apr 13 '08 #3

P: 2
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?
Apr 14 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.