I have a table with a few thousand rows. The table contains two columns, name and email. I have several duplicate rows, for example:
What would be the easiest way to delete all duplicate results. For example, such that the table's content would = SELECT name, DISTINCT(email) FROM table.
You could pretty easily do this by selecting that query into another table, then renaming it to replace the original.
CREATE TABLE `table2` (
`name` varchar(255),
`email` varchar(255),
UNIQUE KEY `email` (`email`));
INSERT INTO `table2` SELECT `name`, DISTINCT(`email`) FROM `table`;
RENAME TABLE `table` TO `table1`;
RENAME TABLE `table2` TO `table`;
Note that this CREATE should be adjusted to your actual table format. I added the unique key on the email field as a suggestion on how you would prevent duplicates in the first place.
Alternatively, you could loop over this
DELETE FROM `table`
WHERE `email` IN (
SELECT `email` FROM `table` GROUP BY `email` HAVING count(*) > 1
) LIMIT 1
Which would delete one duplicate record per call. The importance of the limit is to not remove both rows for any duplicate
The easiest way would be to copy all distinct values into a new table:
select distinct *
into NewTable
from MyTable
DELETE FROM table
WHERE id
NOT IN
(SELECT A.id
FROM
(
SELECT name,MAX(id) AS id
FROM table
GROUP BY name
) A
)
Add an auto-increment field to the table. I believe that when you add it, it will be 'backfilled' for you. Since MySql doesn't allow a delete based on a subquery against the same table, the easiest solution is to then dump the whole dataset into a temptable for use in processing. Assuming you called the new field RowId and the temp table tempTable, you can then use code like this:
DELETE FROM NameAndEmail
LEFT JOIN
( SELECT name, email, Max(RowId) as MaxRowId
FROM temptable
GROUP BY name, email
) as MaxId
WHERE NameAndEmail.Email = MaxId.Email
and NameAndEmail.Name = MaxId.Name
and NameAndEmail.RowId <> MaxId.RowId
The simplest way to clean up a table with duplicate data - is to just add a unique index:
set session old_alter_table=1;
ALTER IGNORE TABLE `table` ADD UNIQUE INDEX (name, email);
Pay particular attention to the first sql statement, without it the IGNORE flag is ignored and the alter table statement will fail with an error.
