Delete Duplicate email addresses from Table in MYSQL

I have a table with columns for ID, firstname, lastname, address, email and so on.

Is there any way to delete duplicate em开发者_JS百科ail addresses from the TABLE?

Additional information (from comments):

If there are two rows with the same email address one would have a normal firstname and lastname but the other would have 'Instant' in the firstname. Therefore I can distinguish between them. I just want to delete the one with first name 'instant'.

Note, some records where the firstname='Instant' will have just 1 email address. I don't want to delete just one unique email address, so I can't just delete everything where firstname='Instant'.

Please help me out.

---

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

---

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

This keeps the lowest, first inserted id's for every email.

---

While MiPnamic's answer is essentially correct, it doesn't solve the problem of which record you keep and which you throw away (and how you sort out related records). The short answer is that this cannot be done programmatically.

Given a query like this:

SELECT email, MAX(ID), MAX(firstname), MAX(lastname), MAX(address)
FROM customers

makes it even worse - since you are potentially selecting a mixture of fields from the duplicate rows. You'd need to do something like:

SELECT csr2.*
FROM customers csr2
WHERE ID IN (
   SELECT MAX(id)
   FROM customers csr
   GROUP BY email
);

To get a unique set of existing rows. Of course you still need to sort out all the lreated records (hint - that's the IDs ni customers table not returned by the query above).

---

I don't know if this will work in MYSQL (I haven't used it)... but you should be able to do something like the following snippets.

I'd suggest you run them in order to get a feel for if the right data is being selected. If it does work, then you probably want to create a constraint on the column.

Get all of the duplicate e-mail addresses:

SELECT 
    EMAILADDRESS, COUNT(1)
FROM
    TABLE
GROUP BY EMAILADDRESS
HAVING COUNT(1) > 1

Then determine the ID from that gives:

SELECT
    ID
FROM 
    TABLE
WHERE 
    EMAILADDRESS IN (
        SELECT 
            EMAILADDRESS
        FROM
            TABLE
        GROUP BY EMAILADDRESS
        HAVING COUNT(1) > 1
    )

Then finally, delete the rows, based on the above and other constraints:

DELETE 
FROM 
    TABLE
WHERE
    ID IN (
        SELECT
            ID
        FROM 
            TABLE
        WHERE 
            EMAILADDRESS IN (
                SELECT 
                    EMAILADDRESS
                FROM
                    TABLE
                GROUP BY EMAILADDRESS
                HAVING COUNT(1) > 1
            )
    )  
    AND FIRSTNAME = 'Instant'

---

• Duplicate the table structure
• Put a Unique Key on the email of the new table (just for safe)
• Do a INSERT on the new table SELECTING data from the older one GROUPING by the email address

---

Another way to dedeupe using forsvarir answer above but modifying it a bit. This way you can keep which ever record you choose to partition by:

BEGIN TRAN

DELETE 
FROM   [TABLE]
WHERE
ID IN (
    SELECT a.ID
    
    FROM
    (
        SELECT  ROW_NUMBER() OVER(PARTITION BY Email ORDER BY Email) [RowNum], ID, Email
        FROM    [TABLE]
        WHERE   Email IN 
                (
                    SELECT 
                        Email
                    FROM
                        [TABLE]
                    GROUP BY Email
                    HAVING COUNT(1) > 1
                )
    ) a
    WHERE a.RowNum > 1
)

--COMMIT TRAN
--ROLLBACK TRAN

---

You can follow this MySQL query:

DELETE p1
FROM Person p1, Person p2
WHERE p1.email = p2.email
AND p1.id> p2.id;