How to Delete Duplicate Rows in Oracle
Last updated August 25, 2026 · By the SaturnSQL team
Keep the row with the lowest ROWID per duplicate key and delete the rest, or use ROW_NUMBER() OVER (PARTITION BY ...) to mark extra copies for removal.
ROWID and MIN(ROWID)
ROWID is stable enough within a single statement, so comparing every row's ROWID against the minimum ROWID for its duplicate key keeps exactly one survivor and deletes the rest. It isn't permanently fixed, though: row movement, a table shrink, or a flashback operation can change a row's ROWID over time.
DELETE FROM customers c
WHERE c.ROWID > (
SELECT MIN(d.ROWID)
FROM customers d
WHERE d.email = c.email
);ROW_NUMBER alternative
This form is easier to read when there are several key columns. An inline view containing an analytic function isn't itself deletable, since Oracle raises ORA-01732 for that, so wrap it in another SELECT and drive the DELETE off ROWID instead. You can still preview which rows would be removed by running the inner SELECT on its own before switching to the DELETE.
DELETE FROM customers
WHERE ROWID IN (
SELECT rid FROM (
SELECT ROWID AS rid,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY customer_id) AS rn
FROM customers
) WHERE rn > 1
);Both approaches only remove rows where a duplicate key actually exists, so a WHERE filter that accidentally excludes one side of a duplicate pair leaves it untouched. Run the matching SELECT first to confirm the row count before switching it to a DELETE.
Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free