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?
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;
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
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
