[thelist] Merge Duplicate Records in MySQL/PHP

[Fred Jones]

On Fri, Jul 4, 2008 at 1:27 PM, r937 <rudy at r937.com> wrote:
>> I have a dozen other tables which have a foreign key
>> to this table and store additional data about each person,
>> such as favorite food and hair color and education etc.
>
> what were you thinking!!!  separate tables???

Actually, I did not create this schema--it's from a third party tool
which allows the user to define custom fields--organizing those into
sets creates muliple tables. In my opinion, the schema is actually
well thought-out and appropriate for the data and functionality
required.

>>  is there an automated way I can do this [merge the records]
>
> no, but you can write one query per each of these dozen tables and find the
> duplicates easily enough
>
> it isn't going to be pretty but after you've finished, i trust you will have
> a better appreciation for the importance of avoiding duplicates ~before~
> populating your tables

I am uncertain as to whether I will or not. The duplicate records are
a result of repeat users signing up to attend an event. The system
actually comes with a duplicate person detection system. The admin of
this system had it set to check email, first name and last name. Then
he realized that he has a lot of duplicates and now we set it to check
just email. This should stop further duplicates from entering the
system. I was asked, after the fact, to remove the duplicates that do
exist.

In short, I plead innocent on both counts. :)

Thanks,
Fred