Oracle Delete Duplicate Rows but Keep One (4 Methods)

To delete duplicate rows in Oracle but keep one, you first decide which row in each group survives, then delete every other row in that group. The shortest way is a DELETE with a subquery that keeps MIN(ROWID) (or the lowest ID) per group. When you need to keep a specific row, such as the newest one, use ROW_NUMBER() to rank the rows and delete everything ranked 2 or higher.

This tutorial shows four methods on a small sample table, with the expected output after each step. You will also see how to check what will be deleted before you delete it, how NULLs change the result, and how to stop duplicates from coming back.

What counts as a duplicate row

A duplicate is not always a row where every column matches. Usually two rows are duplicates because they share the same business key, for example the same email address or the same payment reference, even though their IDs or timestamps differ.

So before writing any SQL, answer two questions:

• Which columns define a duplicate? These become your GROUP BY or PARTITION BY columns.
• Which row do you keep? The oldest, the newest, the one with the lowest ID, or any one of them if the rows are fully identical.

Oracle gives every row a ROWID, the physical address of the row. The Oracle documentation says a rowid usually identifies a row uniquely, which is why it is the standard tiebreaker when the rows have no ID column of their own. The same documentation warns that a rowid can change (for example after export and import) and that you should not use it as a primary key. Using it inside one DELETE statement is safe; storing it for later is not.

Sample setup

Create two small tables. customers has an ID column, so duplicates differ only in their ID and date. payments_stage has no key at all, so its duplicate rows are completely identical.

CREATE TABLE customers (
  customer_id  NUMBER        NOT NULL,
  full_name    VARCHAR2(50),
  email        VARCHAR2(100),
  city         VARCHAR2(30),
  created_at   DATE
);

INSERT INTO customers VALUES (1, 'Asha Rao',    'asha@example.com',  'Pune',    DATE '2026-01-05');
INSERT INTO customers VALUES (2, 'Ravi Kumar',  'ravi@example.com',  'Chennai', DATE '2026-01-06');
INSERT INTO customers VALUES (3, 'Asha Rao',    'asha@example.com',  'Pune',    DATE '2026-02-10');
INSERT INTO customers VALUES (4, 'Meena Iyer',  'meena@example.com', 'Delhi',   DATE '2026-01-07');
INSERT INTO customers VALUES (5, 'Ravi Kumar',  'ravi@example.com',  'Chennai', DATE '2026-03-01');
INSERT INTO customers VALUES (6, 'Ravi Kumar',  'ravi@example.com',  'Chennai', DATE '2026-03-15');
INSERT INTO customers VALUES (7, 'John Mathew', NULL,                'Kochi',   DATE '2026-01-09');
INSERT INTO customers VALUES (8, 'Priya Nair',  NULL,                'Mumbai',  DATE '2026-01-09');

CREATE TABLE payments_stage (
  payment_ref  VARCHAR2(20),
  amount       NUMBER(10,2),
  paid_on      DATE
);

INSERT INTO payments_stage VALUES ('P-1001',  500, DATE '2026-10-01');
INSERT INTO payments_stage VALUES ('P-1001',  500, DATE '2026-10-01');
INSERT INTO payments_stage VALUES ('P-1002', 1200, DATE '2026-10-02');
INSERT INTO payments_stage VALUES ('P-1003',  750, DATE '2026-10-02');
INSERT INTO payments_stage VALUES ('P-1003',  750, DATE '2026-10-02');
INSERT INTO payments_stage VALUES ('P-1003',  750, DATE '2026-10-02');

COMMIT;

In customers, a duplicate means the same email. Asha has 2 rows and Ravi has 3. John and Priya both have a NULL email, but they are clearly different people. Keep that in mind, it matters later.

Step 1: find the duplicates before deleting

Always look before you delete. A GROUP BY with HAVING COUNT(*) > 1 lists each duplicate key and how many rows share it.

SELECT email, COUNT(*) AS cnt
  FROM customers
 GROUP BY email
HAVING COUNT(*) > 1
 ORDER BY email;

Output (expected output). Notice the last row. GROUP BY puts all NULL values into one group, so John and Priya show up as "duplicates" of each other. If you delete by this grouping without thinking, you will lose a real customer.

See every row and its rank

Counts do not tell you which rows will go. Use ROW_NUMBER() to number the rows inside each group. Row 1 is the one you keep; every row numbered 2 or more would be deleted.

SELECT customer_id, email, created_at,
       ROW_NUMBER() OVER (PARTITION BY email
                          ORDER BY customer_id) AS rn
  FROM customers
 WHERE email IS NOT NULL
 ORDER BY email, customer_id;

Output (expected output; the date format depends on your NLS_DATE_FORMAT setting). Rows 3, 5 and 6 have rn > 1, so they are the ones a "keep the lowest ID" delete will remove. The WHERE email IS NOT NULL filter keeps John and Priya out of it.

Method 1: keep the row with the lowest ID

When the table has a unique ID, the simplest rule is "keep the smallest ID in each group". The subquery returns one surviving ID per email, and the DELETE removes everything else.

DELETE FROM customers
 WHERE email IS NOT NULL
   AND customer_id NOT IN (SELECT MIN(customer_id)
                             FROM customers
                            WHERE email IS NOT NULL
                            GROUP BY email);

3 rows deleted.

Rows 3, 5 and 6 are gone, which matches the rn > 1 rows from Step 1. Querying the table now returns IDs 1, 2, 4, 7 and 8.

Why it works: the subquery reads the table once, groups by email and returns 1, 2 and 4. Every non-NULL email row whose ID is not in that list is a duplicate. Oracle's own DELETE documentation confirms that the WHERE condition can reference the table you are deleting from, so this self-reference is allowed.

Run ROLLBACK; before trying the next method.

Method 2: keep the newest row with ROW_NUMBER

Often the lowest ID is not the row you want. If a customer signed up twice, the latest record may hold the current details. ROW_NUMBER lets you choose any order you like.

Build the list of rows to delete

You cannot filter on an analytic function in the same query level's WHERE clause, because analytic functions are calculated after WHERE. So you compute the rank in an inner query and filter on it outside. Return the ROWID of each loser so the DELETE can target exact rows.

DELETE FROM customers
 WHERE ROWID IN (
         SELECT rid
           FROM (SELECT ROWID AS rid,
                        ROW_NUMBER() OVER (
                          PARTITION BY email
                          ORDER BY created_at DESC, customer_id DESC
                        ) AS rn
                   FROM customers
                  WHERE email IS NOT NULL)
          WHERE rn > 1);

3 rows deleted.

Check what survived

SELECT customer_id, full_name, email, created_at
  FROM customers
 ORDER BY customer_id;

Output (expected output). Asha kept her February row (ID 3) and Ravi kept his 15 March row (ID 6). John and Priya are untouched because NULL emails were filtered out.

Notice the second sort column, customer_id DESC. The Oracle documentation for ROW_NUMBER says that for consistent results the query must use a deterministic sort order. If two duplicates had the same created_at and you sorted only by date, Oracle could keep a different row each time you ran it. Adding a unique column as the last sort key removes that doubt.

Run ROLLBACK; again.

Method 3: fully identical rows with no key (ROWID)

The payments_stage table has no ID. The rows for P-1001 and P-1003 are identical in every column, so it does not matter which copy you keep. ROWID gives you something to tell them apart.

DELETE FROM payments_stage
 WHERE ROWID NOT IN (SELECT MIN(ROWID)
                       FROM payments_stage
                      GROUP BY payment_ref, amount, paid_on);

3 rows deleted.

SELECT payment_ref, amount, paid_on
  FROM payments_stage
 ORDER BY payment_ref;

Output (expected output). One copy of P-1001 and two copies of P-1003 were removed. List every column that defines "the same row" in the GROUP BY; if you group only by payment_ref, two different payments that happen to share a reference would be treated as duplicates.

The same delete with EXISTS

You will also see a correlated version in older code. It deletes a row if another row with the same values and a smaller ROWID exists:

DELETE FROM payments_stage p
 WHERE EXISTS (SELECT 1
                 FROM payments_stage q
                WHERE q.payment_ref = p.payment_ref
                  AND q.amount      = p.amount
                  AND q.paid_on     = p.paid_on
                  AND q.ROWID       < p.ROWID);

It gives the same 3 rows deleted here. The catch is that = is never true when either side is NULL, so rows with a NULL in any compared column are never seen as duplicates. The GROUP BY version groups NULLs together instead. If you want NULLs to count as equal in the EXISTS version, compare with DECODE(q.amount, p.amount, 1, 0) = 1, because DECODE treats two NULLs as a match.

Method 4: rebuild the table when most rows are duplicates

A DELETE writes undo and redo for every row it removes, and maintains every index on the table. If a large share of the table is duplicates, it can be quicker to copy the rows you want into a new table and swap names.

CREATE TABLE payments_clean AS
  SELECT DISTINCT payment_ref, amount, paid_on
    FROM payments_stage;

-- after checking the row count in payments_clean:
DROP TABLE payments_stage;
RENAME payments_clean TO payments_stage;

This is DDL, so it cannot be rolled back, and the new table does not carry over indexes, constraints, grants or triggers from the old one. You must recreate those yourself. Use this method in a maintenance window, after a backup, and only when you know every object that depends on the table.

Stop duplicates from coming back

Deleting duplicates fixes today's data. A unique constraint stops tomorrow's. Once the table is clean, add one on the columns that define a duplicate:

ALTER TABLE customers
  ADD CONSTRAINT customers_email_uk UNIQUE (email);

If duplicates still exist, the statement fails with an error like this:

ORA-02299: cannot validate (APP.CUSTOMERS_EMAIL_UK) - duplicate keys found

NULLs do not block this constraint. The Oracle documentation states that a single-column unique key can contain NULLs, so John and Priya can both keep a NULL email. After the constraint is in place, an insert of an existing email fails with ORA-00001 (unique constraint violated). For loads that must update existing rows instead of failing, a MERGE statement is the usual answer.

To find the offending rows on a big table, Oracle can write them for you with the EXCEPTIONS INTO clause. Create the exceptions table with the utlexcpt.sql script shipped with Oracle (under $ORACLE_HOME/rdbms/admin), then add the constraint with EXCEPTIONS INTO exceptions. The statement still fails, but the rowids of every violating row are stored in the EXCEPTIONS table for you to review.

Common mistakes

• Treating NULL keys as duplicates. GROUP BY and PARTITION BY put all NULLs in one group. Add WHERE key IS NOT NULL unless NULL really means "same".
• Grouping by too few columns. Two rows with the same name are not always the same person. Agree on the duplicate definition with whoever owns the data.
• NOT IN on a nullable column. One NULL in the subquery makes NOT IN match nothing, so the DELETE silently removes 0 rows.
• A sort order with ties in ROW_NUMBER. Without a unique last sort column, the row you keep can change between runs.
• Deleting from an inline view with ROW_NUMBER. Oracle does not allow DELETE through a view or subquery whose query contains analytic functions. Select the ROWIDs in a subquery and delete by ROWID, as in Method 2.
• Forgetting child tables. If other tables reference these rows through a foreign key, the delete fails with ORA-02292 (child record found), or removes child rows too if the key is ON DELETE CASCADE. Re-point child rows to the surviving row first.
• Committing before checking. Run the SELECT version first, compare the row count with the DELETE's "n rows deleted" message, and only then commit.

Performance tips

• Run the duplicate-finding query first and note the count. If it is a small fraction of the table, a single DELETE is usually the right tool.
• If most of the table is duplicates, compare against Method 4. Rebuilding avoids per-row undo and index maintenance on the rows you throw away.
• An index on the duplicate key columns can help the GROUP BY and the later unique constraint, but every extra index also slows the DELETE. Check the plan with EXPLAIN PLAN on your real data.
• Avoid committing inside a loop that deletes row by row from a cursor on the same table. One set-based DELETE is simpler and usually faster.
• On a busy production table, the DELETE locks every row it removes until you commit. Run it when other sessions are not updating the same rows.

Frequently asked questions

How do I delete duplicate rows in Oracle using ROWID?

Group by the columns that define a duplicate, keep MIN(ROWID) for each group, and delete the rest: DELETE FROM t WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM t GROUP BY col1, col2); This is Method 3 above.

How do I delete duplicate rows based on two columns?

Put both columns in the GROUP BY (or in the PARTITION BY of ROW_NUMBER). Rows are then duplicates only when both columns match. Add a WHERE filter if either column can be NULL and NULL should not count as a match.

Can I use DISTINCT to remove duplicates from a table?

No. DISTINCT only removes duplicates from a query's result. It does not change the table. You can use it to build a clean copy with CREATE TABLE AS SELECT DISTINCT, as in Method 4.

How do I keep the latest record and delete older duplicates?

Use ROW_NUMBER with ORDER BY created_at DESC plus a unique tiebreaker, and delete the rows where the number is greater than 1. This is Method 2.

Which method is fastest for deleting duplicates in Oracle?

It depends on how many rows you remove. For a small number of duplicates, a single DELETE with GROUP BY or ROW_NUMBER is fine. When most rows are duplicates, rebuilding the table is often quicker. Test both on a copy of your real data and compare the plans.

Summary

• Decide which columns define a duplicate and which row you keep.
• Find the duplicates with GROUP BY ... HAVING COUNT(*) > 1 and preview the losers with ROW_NUMBER.
• Keep the lowest ID with NOT IN (SELECT MIN(id) ...), or any order you like with ROW_NUMBER and ROWID.
• Use MIN(ROWID) only for rows that are identical in every column.
• Filter out NULL keys unless NULL really means "the same".
• Check the deleted row count, then COMMIT.
• Add a unique constraint so duplicates cannot return.