如何删除重复的条目？ [英] How to delete duplicate entries?

问题描述

我必须向现有的表添加一个唯一约束。这是很好的，除了表已经有数百万行，并且许多行违反了我需要添加的唯一约束。

I have to add a unique constraint to an existing table. This is fine except that the table has millions of rows already, and many of the rows violate the unique constraint I need to add.

删除违规行最快的方法是什么？我有一个SQL语句，找到重复并删除它们，但它永远是运行。是否有另一种方法来解决这个问题？可能要备份表，然后在添加约束后恢复？

What is the fastest approach to removing the offending rows? I have an SQL statement which finds the duplicates and deletes them, but it is taking forever to run. Is there another way to solve this problem? Maybe backing up the table, then restoring after the constraint is added?

推荐答案

例如，您可以：

CREATE TABLE tmp ...
INSERT INTO tmp SELECT DISTINCT * FROM t;
DROP TABLE t;
ALTER TABLE tmp RENAME TO t;

这篇关于如何删除重复的条目？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！