Search code examples
sqloracle-databaseduplicatesdelete-row

Removing duplicate rows from table in Oracle

I'm testing something in Oracle and populated a table with some sample data, but in the process I accidentally loaded duplicate records, so now I can't create a primary key using some of the columns.

How can I delete all duplicate rows and leave only one of them?

Solution

  • Use the rowid pseudocolumn.

    DELETE FROM your_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM your_table
GROUP BY column1, column2, column3);

    Where column1, column2, and column3 make up the identifying key for each record. You might list all your columns.