Mysql Query to delete duplicate email ids

Question

I have a table with multiple email ids and i want to delete duplicate email ids from table:

TABLE:

-----------------------
|ID | Email |
| 1 | test@test.com |
| 2 | test@test.com |
| 3 | test@test.com |
| 4 | test1@test.com |
| 5 | test1@test.com |
| 6 | test1@test.com |
-----------------------

How can I delete the extra email ids and table will have only unique email id?

http://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql http://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql — Salih Erikci, Nov 26 '14 at 08:56

score 1 · Accepted Answer · answered Nov 26 '14 at 08:59

1

Please try below query.This query will be work for you.

DELETE n1 FROM email n1, email n2 WHERE n1.ID > n2.ID AND n1.email = n2.email

answered Nov 26 '14 at 08:59

Nikul

1,025
1
13
33

Manish Jangir · Answer 2 · 2014-11-26T09:05:02.383

You can use the following query to delete all duplicate records except one.

DELETE FROM emails WHERE email_id IN (
         SELECT email_id FROM emails GROUP BY email_id HAVING COUNT(email_id) > 1
         )

In which emails is the name of table and email_id is column name.

You can also use the alternate method. Insert the distinct rows from the duplicate rows table to new temporary table. Delete data from table which has duplicate rows then insert the distinct rows from the temporary table

SELECT DISTINCT * INTO #tmp FROM emails
    DELETE FROM emails
    INSERT INTO emails                
    SELECT * FROM #tmp DROP TABLE #tmp

score 0 · Answer 3 · answered Nov 26 '14 at 08:59

delete table a 
 where id > (select min(id)
               from table b 
              where a.email = b.email)

try running it as a select first to see that it deletes the right rows like this

select * 
  from table a 
 where id > (select min(id)
               from table b 
              where a.email = b.email)

score 0 · Answer 4 · answered Nov 26 '14 at 09:04

If the table does not have any references and is not too large in size, you can try this:

Create a duplicate table with a little different name:

CREATE TABLE new_tbl SELECT * from old_tbl

Copy data to that table using query like this:

INSERT INTO new_tbl SELECT ID, Email from old_tbl group by Email

Then drop the old table:

DROP TABLE old_tbl

Then rename the new table to the old:

RENAME TABLE new_tbl TO old_tbl

Hope this helps

score 0 · Answer 5 · answered Nov 26 '14 at 09:09

0

Delete from email_table where ID NOT IN (select * from (select ID from email_table group by email) temp_table)

answered Nov 26 '14 at 09:09

ryvasquez

158
8

score 0 · Answer 6 · answered Nov 26 '14 at 09:12

0

Try This

ALTER IGNORE TABLE `table_name` ADD UNIQUE INDEX index_name (`Email`);

answered Nov 26 '14 at 09:12

Hamed Kamrava

12,359
34
87
125

Mysql Query to delete duplicate email ids

6 Answers6