MySQL 带条件的 DELETE 查询

发布于 2024-11-17 12:43:18 字数 224 浏览 2 评论 0 原文

我有一个表 PEOPLE，其中包含“firstName”、“lastName”（varchars）和“deleted”（位）列。

我想从此表中删除具有 TRUE 删除属性的条目，但前提是它们与表中另一个单独的条目共享确切的名字和姓氏。

换句话说，从表中删除“已删除”人员，但前提是他们是重复的。

不知道如何做到这一点，尤其是不知道如何快速做到这一点。如有任何帮助，我们将不胜感激，谢谢。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

空心空情空意 2024-11-24 12:43:18

DELETE FROM people
WHERE EXISTS (
    SELECT *
    FROM people p2
    WHERE people.firstName = p2.firstName AND people.lastName = p2.lastName
    GROUP BY firstName, lastName
    HAVING COUNT(*)>1
)
AND deleted = 1 -- True

DELETE FROM people
WHERE EXISTS (
    SELECT *
    FROM people p2
    WHERE people.firstName = p2.firstName AND people.lastName = p2.lastName
    GROUP BY firstName, lastName
    HAVING COUNT(*)>1
)
AND deleted = 1 -- True

回复收藏 0 原文

灯角 2024-11-24 12:43:18

如果您的表具有唯一的主键（...将取决于设计...），那么这是需要计算条目出现次数的可行替代方案：

DELETE FROM people as A
WHERE deleted = 1
AND EXISTS (SELECT '1'
            FROM people as B
            WHERE B.id <> A.id
            AND A.firstName = B.firstName
            AND A.lastName = B.lastName)

这可能比计算行具有稍好的性能。请注意，此查询可能会遇到与上一个答案中存在的相同问题；具体来说，如果有两个或多个“已删除”行，并且没有“未删除”行，则它们都可能会被删除（留下任何行！）。如果查询的目的只是在存在“未删除”等效行时删除“已删除”行，请添加 AND B.deleted = 0 作为内部 WHERE< 的一部分/代码> 子句。

If your table has a unique primary key (... will depend on design...), then this is a viable alternative to needing to count the occurrances of entries:

DELETE FROM people as A
WHERE deleted = 1
AND EXISTS (SELECT '1'
            FROM people as B
            WHERE B.id <> A.id
            AND A.firstName = B.firstName
            AND A.lastName = B.lastName)

This may have slightly better performance than counting rows. Please note that this query will likely suffer the same possible issue present in the previous answer; specifically, if there are two or more 'deleted' rows, and no 'non-deleted', both of them will probably be removed (leaving you with no rows!). If the intent of the query is only to remove 'deleted' rows when there is a 'non-deleted' equivalent row, add AND B.deleted = 0 as part of the inner WHERE clause.