如何删除重复条目? [英] 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屋！