Removing duplicate records in a table 1

[hallm]

I've got a table with existing data that I'm using in a fox pro database, it has a bunch of duplicate records that were keyed in by mistake over time. I want to create a primary index and can't becase of that. Is there a way within Fox Pro 8 to automate removal of duplicate records.

 

[Mike Gagnon]

Check out FAQ184-424.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Olaf Doschke]

Make an Index on an expression which should be unique:

INDEX ON UniqueExpression(Fields) TAG Uniq UNIQUE

This will allow duplicates, but only the first record matching the expression will be indexed. Then you can DELETE ALL / SET ORDER TO Uniq / RECALL ALL and have all duplicates deleted afterwards. PACK and they are gone.

Example:
You have a table with fields firstname, lastname, birthdate and some other fields. You want no duplicates in these so:

SELECT 0
USE myTable IN 0 EXCLUSIVE
INDEX ON firstname+lastname+dtos(birthdate) TAG Uniq UNIQUE
SET ORDER TO
DELETE ALL
SET ORDER TO Uniq
RECALL ALL
PACK

Be cautious, that the expression really is sufficient to find not too much and not too less duplicates. In case of persons there still might be two people with same name and birth date.

Bye, Olaf.

 

[Stella740pl]

Olaf,
You should consider putting your clever method into FAQ on "Microsoft: VFP - Databases, SQL&VFP, and Reports" forum.

 

[Olaf Doschke]

Hi Stella,

Thanks for the compliment. Well, perhaps it's time for me to start writing FAQs.

But there is still a catch: Indexing is done for both deleted and undeleted records. My method would not take that into account, and then you could finally end up with a "John Smith", which originally was deleted, replacing some other undeleted "John Smith", which was intended to be the right unique record that should have remained! An initial PACK would perhaps be the solution to that...

I'd test this method on some conditions and settings that would influence the result and then make a FAQ out of it...

Bye, Olaf.

 

[hallm]

Thank you. I haven't done anything with this yet, but I think a combination of both methods are going to work perfect. One to list my dupes and the other to delete them.


Thanks again.

Marion

 

[foxdbs]

-> OlafDoschke:

Maybe with SET FILTER TO NOT DELETED() for whole process it can work well?

 

[foxdbs]

-> hallm:

Of course, it is necessary to remove duplicities.
However for the primary key maybe it's better to use a system internal number (1,2,3,4,...), with no meaning in the real word.