How to delete duplicate records in my database? [duplicate]

Posted on

Question :

I joined about 12 databases into 1 db and in each database I have some duplicate records.

How can I delete all duplicate records in my joined database?

For example, I want to delete duplicate records when data in column “phone” is the same.

Answer :

I’m assuming that you have no foreign key constraints on the phone column and you have a Primary Key defined:

DELETE FROM foo_schema.bar_table
WHERE primary_key_column NOT IN (SELECT MAX(primary_key_column)
                                        FROM foo_schema.bar_table
                                        GROUP BY phone)

This should group all records by the column phone, and drop all the duplicates but the one with the max PK. Hope that helps.

Please try this on a dummy table before attempting this on your actual table.

Leave a Reply

Your email address will not be published. Required fields are marked *