How to delete Duplicates in MySQL table

by

Many ways lead to Rome. This is one. It is very fast. So you can use it with big databases. Don’t forget the indeces.
The trick is: make phoneNo unique and use “ignore”.

drop table if exists bkPhone_template;
create table bkPhone_template (
         phoneNo varchar(20),
         firstName varchar(20),
         lastName varchar(20)
 );

insert into bkPhone_template values('0783313780','Brady','Kelly');
 insert into bkPhone_template values('0845319792','Mark','Smith');
 insert into bkPhone_template values('0834976958','Bill','Jones');
 insert into bkPhone_template values('0845319792','Mark','Smith');
 insert into bkPhone_template values('0828329792','Mickey','Mouse');
 insert into bkPhone_template values('0834976958','Bill','Jones');

drop table if exists bkPhone;
create table bkPhone like bkPhone_template;
alter table bkPhone add unique (phoneNo);

insert  ignore into bkPhone (phoneNo,firstName,lastName) select phoneNo,firstName,lastName from bkPhone_template;

drop table bkPhone_template;

If the data table already exists, then you only have to run a create table select with a following insert ignore select. At the end you have to run some table renaming statements. That’s all.

This workaround is much,much faster then a delete operation.

More Related Contents:

  1. MySQL query finding values in a comma separated string
  2. In MySQL queries, why use join instead of where?
  3. SQLite INSERT – ON DUPLICATE KEY UPDATE (UPSERT)
  4. Can table columns with a Foreign Key be NULL?
  5. MySQL not using indexes with WHERE IN clause?
  6. MySQL dump by query
  7. single fixed table with multiple columns vs flexible abstract tables
  8. How many rows in a database are TOO MANY?
  9. SQLite – ORDER BY RAND()
  10. SQL – How to transpose?
  11. Which SQL query is better, MATCH AGAINST or LIKE?
  12. MySQL equivalent of DECODE function in Oracle
  13. Best database field type for a URL
  14. Query that ignore the spaces
  15. How to Optimize Queries in a Database – The Basics
  16. MySQL – how to front pad zip code with “0”?
  17. Create date from day, month, year fields in MySQL
  18. Find rows that have the same value on a column in MySQL
  19. what is the difference between GROUP BY and ORDER BY in sql
  20. How do I list all the columns in a table?
  21. Two single-column indexes vs one two-column index in MySQL?
  22. VARCHAR as foreign key/primary key in database good or bad?
  23. How to find all IDs of children recursively?
  24. Transpose latest rows per user to columns
  25. Need to list all triggers in SQL Server database with table name and table’s schema
  26. What is the purpose of constraint naming
  27. Multiple Ranks in one table
  28. Comma separated values in a database field
  29. In SQL how do I get the maximum value for an integer?
  30. Get the first and last date of next month in MySQL

Leave a Comment