Deleting Duplicate Rows in Oracle

A quick guide to deleting duplicate rows in Oracle.

Test Setup:

CREATE TABLE dupTest (
col1 VARCHAR2(30),
col2 VARCHAR2(30)
);
/

INSERT INTO dupTest VALUES ('A','Ant');
INSERT INTO dupTest VALUES ('A','Ant');
INSERT INTO dupTest VALUES ('B','Bear');

SELECT * FROM dupTest;


COL1 COL2
------ ----
A Ant
A Ant
B Bear

To delete the duplicate rows, check that the rowid is in a select of the max rowids from the table where the count is greater than 1.

DELETE FROM dupTest
WHERE ROWID IN ( SELECT MAX(ROWID)
FROM dupTest
GROUP BY col1, col2
HAVING COUNT(*) > 1 );

SELECT * FROM dupTest;


COL1 COL2
------ ----
A Ant
B Bear

The duplicate row has been deleted.

And finally cleanup:

DROP TABLE dupTest;

De-duped