472,103 Members | 1,703 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,103 software developers and data experts.

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)

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
3 2614
1,134 Expert 1GB
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
1,134 Expert 1GB
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
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.

Similar topics

3 posts views Thread by C.P. | last post: by
1 post views Thread by Chris Godwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.