从MYSQL中的表中删除重复的电子邮件地址 [英] Delete Duplicate email addresses from Table in MYSQL

问题描述

我有一张表，列有 ID ， firstname ， lastname ，地址，电子邮件等等。

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

有没有办法从TABLE中删除重复的电子邮件地址？

Is there any way to delete duplicate email addresses from the TABLE?

附加信息（来自评论）：

Additional information (from comments):

如果有两行具有相同的电子邮件地址中的一个将具有正常的 firstname 和 lastname 在 firstname 中强>即时。所以我可以区分他们。我只想删除一个名为'即时'的人。

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'.

请注意，某些记录中 firstname ='Instant'将只有一个电子邮件地址。我不想删除一个唯一的电子邮件地址，所以我不能删除所有 firstname ='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'.

请帮助我。

推荐答案

我不知道这是否可以在MYSQL中工作（我没有使用它）...但你应该可以做以下代码片段。

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

然后确定ID中的ID：

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'

这篇关于从MYSQL中的表中删除重复的电子邮件地址的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！