[RESOLVED] How do I get rid of duplicate records?

ppowell

I accidentally created duplicate records upon attempting to migrate data from one server to another. You will have 2, 3 or more records with every single field identical except for the ID. Best way to know they're dups is by "application_date" along with "unique_key".

How do I get rid of the dups?

Thanx
Phil

Roger_Ramjet

The most foolproof way to do this is to copy the data into a new table with a new id column. Simply select all fields EXCEPT the id column and use GROUP BY on all columns and you will have one copy of each record.

Now, if you can't do that because data in other tables already is linked to the id column then a similar approach is required, with a little trickery. Same as above only this time you include the id cilumn and GROUP on all, but you use Min(id) for the id column. Now you have only one copy of each record with the first id preserved.

Of course if any other record has had it's id used in a related table then you are going to have to resort to more slick stuff. You will have to update the related ids by joining the old table to the new table on your unique_key and updating the related record with the id from the new table.

Make sure that you do it via a new table. I know some here are going to say you can join the table to itself and delete the duplicates (probably by using a subquery to get one unique id), but believe me, it is far better to make a new table and keep the old one around with a new name for a while just in case something goes wrong: you have not lost your data that way and can start all over again.

ppowell

could you tell me how you use Min(id) for the id column? I tried doing this:

GROUP BY min(s.id)

But that produced the error: "ERROR 1111 (HY000): Invalid use of group function"

Phil

Roger_Ramjet

So you are telling me that there are related records in other tables?

As to the use of Aggregate Functions with the Gropu BY Clause, they are so usefull and important, and yet very straightforward, that I'd really rather you read the manual and bookmarked it for future reference.

$sql = "SELECT MIN(id), student_first_name, student_last_name, email, application_date, modification_date, unique_key 
     FROM student
     GROUP BY id, student_first_name, student_last_name, email, application_date, modification_date, unique_key";

ppowell

Roger Ramjet wrote:
So you are telling me that there are related records in other tables?

There are other tables that work as association tables between student ID and other IDs such as ethnicity, etc., so yes there can be related records in other tables

Roger Ramjet wrote:
As to the use of Aggregate Functions with the Gropu BY Clause, they are so usefull and important, and yet very straightforward, that I'd really rather you read the manual and bookmarked it for future reference.
$sql = "SELECT MIN(id), student_first_name, student_last_name, email, application_date, modification_date, unique_key 
     FROM student
     GROUP BY id, student_first_name, student_last_name, email, application_date, modification_date, unique_key";

Oh crap, I knew that! I thought you were referring to using the MIN() function within the GROUP BY clause

Phil

Roger_Ramjet

OK, so now you can select unique records into your new table.

I take it that you know how to then join the two tables and use them to update the related tables and overwrite the ids you want to discard.

ppowell

No, I apparently do not.. as I tried this, and it failed

select min(id) AS id, student_first_name, student_last_name, email, application_date, modification_date, unique_key from student group by id, student_first_name, student_last_name, email, application_date, modification_date

The query did not break but no duplicates were "elminated"; they still appeared in the resultset.

Phil

Roger_Ramjet

First thing create the new students table to hold the clean data - I'm going to call it new. It will have exactly the same structure as the existing table except that the unique_key should have a unique index to prevent this happening again.

$sql1 = "INSERT INTO new
     SELECT MIN(id), student_first_name, student_last_name, email, application_date, modification_date, unique_key
     FROM student
     GROUP BY id, student_first_name, student_last_name, email, application_date, modification_date, unique_key":

Check it when you're done. The data should be correct but check it anyway.

Now, to clean up related tables you do this: (I'm going to sue a tabel called example)

$sql = "UPDATE example e INNER JOIN students s ON (e.student_id=s.id)
     INNER JOIN new n ON (s.id=n.id)
     SET e.student_id=n.id)";

// or you could use
$sql = "UPDATE example e INNER JOIN students s ON (e.student_id=s.id)
     INNER JOIN new n ON (s.unique_key=n.unique_key)
     SET e.student_id=n.id)";

That will update the related table with the id from the clean data no matter what id it had from the dirty table.

ppowell

Did you mean to use a third table "example"? I'm confused here and getting the "Table blah.example does not exist" when attempting to update. I created the "temporary table" that does indeed have unique records in it, however.

Phil

Roger_Ramjet

As I said, I was using the name example as an example because I have no idea what your related tables are called. Just substitute the real table names and column names fo any realted tables that may contain the bad ids.

Now, calling the new table the 'temporary' table is not right - it is your new students table in all but name. So once you are satisfied it is correct, rename the students table to something like old_students and then rename the new table to students. In a while you can delete the old-students, once you are certain that the new table is functioning correctly and has all the data you want.

ppowell

Thanx for the suggestions.. I couldn't interpret what you meant in a timely fashion so I wound up creating the temporary table and deleting from the original table, and the dups were all gone while the data remains intact.

Thanx again!
Phil

Roger_Ramjet

OK, so now yu need to check for orphans in all related tables. The query structure is related table left join students table where students.id is null. That will list any orphans in the related table

You should have a complete set of such queries already coded as part of your maintanence plan. Run them all on a regular schedule. You also should have a complete set of queries coded to search for duplicates in every table, also run as part of your maintanence plan.

ppowell

I did that the very un-DBA way but the "Phil way": I wrote PHP scripts that deleted all associated records and re-inserted them into their respective association tables.

Phil

Roger_Ramjet

Phil, the DBA way is the way that works. Nothing else matters.

In computing, there is stuff that works and stuff that doesn't: if it works then it is right, and RIGHTEOUS.

Pragmatism Rules.

Roger_Ramjet

But you still need a maintenance plan. Just code all the queries for orphans, etc and a page with one button to run them all; than click it once a week. Call it pre-emptive maintenance.