Delete duplicate rows in Oracle

Topic Introduction: In this tutorial, we will show how to delete duplicate rows in Oracle.

Method #1

DELETE FROM employees

      WHERE ROWID NOT IN (  SELECT MIN (ROWID)

                              FROM employees

                          GROUP BY LAST_NAME);

Method #2

DELETE FROM employees

      WHERE ROWID IN

                (SELECT rid

                   FROM (SELECT ROWID

                                    rid,

                                ROW_NUMBER () 

                                OVER (PARTITION BY LAST_NAME ORDER BY ROWID)

                                    rn

                           FROM employees)

                  WHERE rn <> 1);

Method #3

DELETE FROM employees a

      WHERE a.ROWID > ANY (SELECT b.ROWID

                             FROM employees b

                            WHERE a.LAST_NAME = b.LAST_NAME);