Hello!

I have a table with a column (nr) that miss a unique index and therefore also contains duplicates. (The table also have an identity column) I use the following question to identify which rows that are duplicates:

select count(nr) as Total, nr
from tTable
group by nr
having count(nr) > 1

The result might look like this
Total nr
3 1

What I would like to know is how I can delete all of the rows with nr = 1 but one row that I would like to keep in the table.

Thanks in advance!

Boson

Delete from tTable where nr=1 and ID not in (select min(ID) from tTable where nr = 1)

ID is the identity field. Make sure the nr field is indexed, as that will add to the performance.

Brian

A more general solution is discussed in this article:

http://support.microsoft.com/default...NoWebContent=1

Pay particular attention to the comment the article makes, "...you should closely examine the process which allowed the duplicates to happen in order to prevent a recurrence." ;)


Jeff Mason
Custom Apps, Inc.
www.custom-apps.com