Finding Duplicates 1

[fiber0pti]

I have a field called customer ID. I want to make it a primary key but it says there are duplicates. How do I find these so I can delete one of them? There's abuot 4,000 records so I can't really look through them.

 

[foxdev]

[tt]select customerid, count(*)
from mytable
group by customerid
having count(*) > 1 [/tt] Robert Bradley
Support operation moo!
Visit the OpCow page

http://www.agrainofhope.org/opcow.shtml

to help in this cause

 

[ccuster]

Hi,

Here is what I do to rid of dupes:

Create a table with same structure,
I do this with
select top 1 into temptab from maintable

truncate temptab

insert into temptab
(Customerid)
(select distinct customerid from maintable)

Update temptab
set temptab.field1=maintable.field1,
temptab.field2=maintable.field2
from temptab, maintable
where temptab.customerid=maintable.customerid

You can then rename the table to maintable.
Then set primary key.

I would keep the main table to make sure you did not remove any data you wanted to keep.

HTH, Chuck