Removing duplicate rows from table in Oracle

Question

Hi, 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? This is in Oracle

Thanks

Answer 1

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, column3 make up the identifying key for each record. You might list all your columns.

Answer 2

From DevX.com:

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

Where column1, column2, etc. is the key you want to use.

Answer 3

You should do a small pl/sql block using a cursor for loop and delete the rows you don't want to keep. For instance:

declare
prev_var my_table.var1%TYPE;

begin

for t in (select var1 from my_table order by var 1) LOOP

-- if previous var equal current var, delete the row, else keep on going.
end loop;

end;

Answer 4

The example comes from MS SQL, but should be similar for oracle.

delete top (1) from table where primary_key=[somevalue]

you run this query in a cursor with all duplicates key. You run cursor until all duplicates will be deleted.

The Oracle equivalent is:

DELETE from table where primary_key=[somevalue] AND rownum=1

Answer 5

DELETE FROM tablename a
      WHERE a.ROWID > ANY (SELECT b.ROWID
                             FROM tablename b
                            WHERE a.fieldname = b.fieldname
                              AND a.fieldname2 = b.fieldname2)