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.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.