How to delete Duplicates in MySQL table.

Question

I've given a client the following query to delete duplicate phone no. records in an MSSQL database, but now they need to also do it on MySQL, and they report that MySQL complains about the format of the query. I've included the setup of a test table with duplicates for my code sample, but the actual delete query is what counts.

I'm asking this in ignorance and urgency, as I am still busy downloading and installing MySQL, and just maybe somebody can help in the mean time.

 create table bkPhone
 (
  phoneNo nvarchar(20),
  firstName nvarchar(20),
  lastName nvarchar(20)
 )
 GO

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

 alter table bkPhone add phoneId int identity

 delete from bkPhone
 where phoneId not in
 (
  select min(phoneId)
  from bkPhone
  group by phoneNo,firstName,lastName
  having  count(*) >= 1
 )

Answer 1

Mysql also included:

http://mssql-to-postgresql.blogspot.com/2007/12/deleting-duplicates-in-postgresql-ms.html

Answer 2

You can select out the unique ones by:

select distinct(phoneNo) from bkPhone

and put them into another table, delete the old table and rename the new one to the old name.

Answer 3

MySQL complains, because it makes no sense. You trying to aggregate using min() column by which you group.

Now, if you're trying to delete duplicate phone numbers for the same person, the SQL should be:

delete from bkPhone
 where phoneId not in
 (
         select min(phoneId)
         from bkPhone
         group by firstName,lastName /* i.e. grouping by person and NOT grouping by phoneId */
         having  count(*) >= 1
 )

Answer 4

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.