Easiest way to delete duplicate rows? [message #433657] |
Thu, 03 December 2009 09:22  |
ingarb
Messages: 4 Registered: December 2009 Location: Oslo, Norway
|
Junior Member |
|
|
Hi.
If I have a table structured like this, what is the easiest way to delete duplicates? I want column B to only have unique records.
ID B C D
-------------
1 2 2 2
2 2 3 9
3 2 4 7
4 6 9 6
After my delete, i should have two rows. (1 and 4). Which ones of the records I delete that have the same value in the B column, I don't care.
I tried a solution where I selected all columns and took out the min(ID) on each unique value in the B column, and then deleted all those records in a script, but it takes too long. The table is really really large.
Isn't there a shorter, easier way to do it?
Thanks for any help.
|
|
|
|
Re: Easiest way to delete duplicate rows? [message #433697 is a reply to message #433657] |
Thu, 03 December 2009 12:46   |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
ingarb wrote on Thu, 03 December 2009 10:22
ID B C D
-------------
1 2 2 2
2 2 3 9
3 2 4 7
4 6 9 6
After my delete, i should have two rows. (1 and 4). Which ones of the records I delete that have the same value in the B column, I don't care.
Then why should you have 1 and 4 and not 2 and 4 or 3 and 4?
[edit] forgot the hint
Look at the analytic function LEAD with PARTITION. There are many way to delete duplicates, some more efficient than others, but in searching I am sure you will come across one with LEAD and PARTITION that you can fir into your data model.
[Updated on: Thu, 03 December 2009 12:50] Report message to a moderator
|
|
|
|
|
Re: Easiest way to delete duplicate rows? [message #433829 is a reply to message #433750] |
Fri, 04 December 2009 07:51   |
 |
LKBrwn_DBA
Messages: 487 Registered: July 2003 Location: WPB, FL
|
Senior Member |
|
|
True, I found out that you may have the same SCN number if you loaded/modified the duplicate rows in the same transaction.
Therefore, if you don't care which of the duplicate rows to keep, use the standard solution:
DELETE FROM Mytable D
WHERE ROWID < (SELECT MAX (ROWID)
FROM Mytable M
WHERE M.B = D.B);
[Updated on: Wed, 20 October 2010 12:13] by Moderator Report message to a moderator
|
|
|
Re: Easiest way to delete duplicate rows? [message #435104 is a reply to message #433657] |
Mon, 14 December 2009 05:43   |
ingarb
Messages: 4 Registered: December 2009 Location: Oslo, Norway
|
Junior Member |
|
|
Thanks for your replies.
Is "ORA_ROWSCN" implemented in Oracle 9i ? Running that SQL just produces the error ''ORA-00092: "ORA_ROWSCN" Invalid ID''.
But I used the SQL with ROWID, and that worked fine.
Thanks again.
|
|
|
|
|