0

i am trying to delete e-mail duplicates from table nlt_user this query is showing correctly records having duplicates:

select [e-mail], count([e-mail])
from nlt_user
group by [e-mail]
having count([e-mail]) > 1

now how can i delete all records having duplicate but one? Thank you

HoneyBadger
  • 14,750
  • 3
  • 34
  • 48
papacico
  • 169
  • 3
  • 10

3 Answers3

2

If MySQL version is prior 5.7.4 you can add a UNIQUE index on the column e-mail with the IGNORE keyword. This will remove all the duplicate e-mail rows:

ALTER IGNORE TABLE nlt_user
ADD UNIQUE INDEX idx_e-mail (e-mail);

If > 5.7.4 you can use a temporary table (IGNORE not possible on ALTER anymore):

CREATE TABLE nlt_user_new LIKE nlt_user;
ALTER TABLE nlt_user_new ADD UNIQUE INDEX (emailaddress);
INSERT IGNORE INTO nlt_user_new SELECT * FROM nlt_user;
DROP TABLE nlt_user;
RENAME TABLE nlt_user_new TO nlt_user;
Thibault Clement
  • 2,360
  • 2
  • 13
  • 17
  • this sound ad the best solution but it is not working: #1062 - Duplicate entry 'mail@mail.com' for key 'idx_e-mail' – papacico Jan 27 '16 at 10:40
  • Which version of MySQL do you have ? – Thibault Clement Jan 27 '16 at 10:56
  • Server version: 5.5.42-cll - MySQL Community Server (GPL) – papacico Jan 27 '16 at 11:00
  • According to MySQL documentation` ALTER IGNORE` should work with 5.5.42: "IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table or if warnings occur when strict mode is enabled. [...] If IGNORE is specified, only one row is used of rows with duplicates on a unique key. The other conflicting rows are deleted. Incorrect values are truncated to the closest matching acceptable value. As of MySQL 5.7.4, the IGNORE clause for ALTER TABLE is removed and its use produces an error." – Thibault Clement Jan 27 '16 at 11:09
  • thanks for your effort, i have chosen Code-monk solution wich is quicker – papacico Jan 27 '16 at 11:15
1

Try this :

delete n1 from nlt_user n1
inner join nlt_user n2 on n1.e-mail=n2.e-mail and n1.id>n2.id;

This will keep record with minimum ID value of duplicates and deletes remaining duplicate records

Abhishek Ginani
  • 4,511
  • 4
  • 23
  • 35
0

The rank function can be employed to retain only the unique values

1:Create a new table which contains only unique values

Example: nlt_user_unique

CREATE TABLE  nlt_user_unique AS
(SELECT * FROM
(SELECT A.*,RANK() OVER (PARTITION BY email ORDER BY email) RNK 
 FROM nlt_user A)
 where RNK=1)

2:Truncate the orignal table containing duplicates

truncate table nlt_user

3:Insert the unique rows from the table created in step 1 to your table nlt_user

INSERT INTO nlt_user()
SELECT email from nlt_user_unique;
SidD89
  • 21
  • 4