How to delete duplicate entries?

Question

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.

What is the fastest approach to removing the offending rows? I have an SQL statement that 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?

Answer 1

You can do like this:

Example:

CREATE TABLE tmp ...

INSERT INTO tmp SELECT DISTINCT * FROM t;

DROP TABLE t;

ALTER TABLE tmp RENAME TO t;