How do I remove duplicates row in SQL?

Question

My table name is emails.

My table structures looks like:

I want to remove all of the duplicated emails. I tried this query:

DELETE FROM emails WHERE email NOT IN (SELECT MIN(email)
    FROM emails GROUP BY email) 

But with no result. Can someone help me with this?

Answer 1

The query that you are looking for would use id, not email:

DELETE FROM emails
    WHERE id NOT IN (SELECT MIN(id) FROM emails GROUP BY email) ;

EDIT:

You are using MySQL. You can get around this with the subquery hack:

DELETE FROM emails
    WHERE id NOT IN (select minid from (SELECT MIN(id) as minid FROM emails GROUP BY email) e) ;

Or, you can use a join:

delete e
    from emails e left outer join
         (select min(id) as minid
          from emails
          group by email
         ) em
         on e.id = e.minid
    where em.id is null;

Answer 2

Try this instead

--CREATE a Temporary table

create table IDsToRemove (ID Int)
INSERT INTO IDStoRemove
     SELECT MIN(id) _
            FROM emails GROUP BY email 


      DELETE FROM emails WHERE id NOT IN (SELECT id from IDStoRemove) 

I don't know the exact mySQL syntax, but should give you the idea

Answer 3

Maybe you (or someone) wants to delete records that are unique, I'll just leave this stolen answer here[0].

DELETE Emails 
FROM Emails
  LEFT OUTER JOIN (
    SELECT MIN(id) as id, email, pwd
    FROM Emails
    GROUP BY email, pwd
  ) as KeepRows ON
  Emails.id = KeepRows.id
WHERE
  KeepRows.id IS NULL

0.How can I remove duplicate rows?