views:

2213

answers:

5

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

+9  A: 

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.

Bill the Lizard
From here: http://www.devx.com/tips/Tip/14665
Bill the Lizard
+1 I had to find two duplicate phone numbers buried in 12,000+ records. Changed the DELETE to SELECT and this found them in seconds. Saved me a ton of time, thank you.
shimonyk
+2  A: 

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.

Mark
A: 

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;
Nick
I believe the downvote is because you are using PL/SQL when you can do it in SQL, incase you are wondering.
WW
Just because you can do it in SQL, doesn't mean its the only solution. I posted this solution, after I had seen the SQL-only solution. I thought down votes were for incorrect answers.
Nick
A: 

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
Michal Dymel
You don't have Top (1) in Oracle...
Juan Manuel
A: 
DELETE FROM tablename a
      WHERE a.ROWID > ANY (SELECT b.ROWID
                             FROM tablename b
                            WHERE a.fieldname = b.fieldname
                              AND a.fieldname2 = b.fieldname2)
Eryan