Deleteing duplicate records

I have a simple table with phone numbers.    Field name : Phone.    There are many duplicates in there.  I want to delete the duplicates.  Simple table one field name phone with about 10,000 phone numbers.

Any one have an easy way of doing there?

Thank You.

D
MalibucompanyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
since your table ONLY contains 1 field and you wish to remove the duplicate records, you probably can do this... (for a quick solution)

1. Copy the records into a Temp table:
Select * into #temp from yourTable

Open in new window

2. Delete the records from your Table:
Delete from yourTable

Open in new window

3. Reinsert back the records from Temp table into your Table:
Insert into yourTable Select field1 from #temp group by field1

Open in new window

I have over 15 years didn't use FoxPro so the codes above are not tested. Backup your existing table before you run the scripts above.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Olaf DoschkeSoftware DeveloperCommented:
Taking Ryans idea you'd do

Select Phone from yourtable Group By Phone Into Cursor curSingleNumbers

Open in new window

then ZAP the original table (empty it) and reinsert via
Append From DBF("curSingleNumbers")

Open in new window

or you could
Insert Into Yourtable (Phone) Select Phone From curSingleNumbers

Open in new window


I second to do this on a copy, at the stage you ZAP the original table you only have data in memory.

Another way without secondary table:
USE yourtable EXCLUSIVE
INDEX ON Phone TAG xUniq UNIQUE
SET ORDER TO
DELETE ALL && marks all data deleted
SET ORDER TO TAG xUniq
RECALL ALL && recalls first occurrances of any phone number only
DELETE TAG xUniq
PACK

Open in new window


All theses methods will only take care of 100% identical values, no duplicates with just some spaces or hyphens or brackets as difference will be removed, you have to do data cleanising of such unneeded chars as a preprocessing step.

Bye, Olaf.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FoxPro

From novice to tech pro — start learning today.