Remote mysql data duplication

Question

This shows me all the first and last names that have exactly two entries, identical

SELECT `firstname`,`lastname`,COUNT(*) AS Count FROM `people` GROUP BY `firstname`,`lastname` HAVING Count = 2 

How to turn this into a DELETE FROM WHERE statement using LIMIT to remove only one from each record and leave the other.

okay, it seems this is the way to the technical, i'm just going to do it in php while loop

Answer 1

You can create a table with 1 record for each of the duplicates: Then delete all duplicate records from the people table, and then reinsert the duplicate records.

 -- Setup for example create table people (fname varchar(10), lname varchar(10)); insert into people values ('Bob', 'Newhart'); insert into people values ('Bob', 'Newhart'); insert into people values ('Bill', 'Cosby'); insert into people values ('Jim', 'Gaffigan'); insert into people values ('Jim', 'Gaffigan'); insert into people values ('Adam', 'Sandler'); -- Show table with duplicates select * from people; -- Create table with one version of each duplicate record create table dups as select distinct fname, lname, count(*) from people group by fname, lname having count(*) > 1; -- Delete all matching duplicate records delete people from people inner join dups on people.fname = dups.fname AND people.lname = dups.lname; -- Insert single record of each dup back into table insert into people select fname, lname from dups; -- Show Fixed table select * from people; 

Answer 2

if you have a primary key, for example id, you can do:

 delete from people where id not in ( select minid from (select min(id) as minid from people group by firstname, lastname) as newtable ) 

the subtitle bit select min(id)... provides you with unique (identifier-based) lines for a given combination of first name, last name; and then you delete all other lines, i.e. your duplicates. You need to wrap your subquery due to an error in mysql, otherwise we could do:

 delete from people where id not in ( select min(id) as minid from people group by firstname, lastname ) 

it would be better:

 delete people from people left outer join ( select min(id) as minid from people group by firstname, lastname ) people_grouped on people.first_name = people_grouped.first_name and people.last_name = people_grouped.last_name and people_grouped.id is null 

to avoid a subquery.

Answer 3

Create a new table and add a unique key (first name, last name). Then insert the rows into the old table into the new table. Then rename the tables.

 mysql> select * from t; +-----------+----------+ | firstname | lastname | +-----------+----------+ | A | B | | A | B | | X | Y | +-----------+----------+ 3 rows in set (0.00 sec) mysql> create table t2 like t; Query OK, 0 rows affected (0.00 sec) mysql> alter table t2 add unique key name(firstname,lastname); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert ignore into t2 select * from t; Query OK, 2 rows affected (0.00 sec) Records: 3 Duplicates: 1 Warnings: 0 mysql> select * from t2; +-----------+----------+ | firstname | lastname | +-----------+----------+ | A | B | | X | Y | +-----------+----------+ 2 rows in set (0.01 sec)