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.

  • Do you really want to delete the data or just not show them in the query results?
    – jkavalik
    May 25 '16 at 6:10
  • @jkavalik yeah, I'm sure, I want to delete duplicates.
    – John Doe
    May 25 '16 at 6:11

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.


Not the answer you're looking for? Browse other questions tagged or ask your own question.