Home » SQL & PL/SQL » SQL & PL/SQL » Easiest way to delete duplicate rows? (Toad v10, Oracle 9i Release 2, Windows 7)
Easiest way to delete duplicate rows? [message #433657] Thu, 03 December 2009 09:22 Go to next message
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 #433662 is a reply to message #433657] Thu, 03 December 2009 09:37 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
>Isn't there a shorter, easier way to do it?

It can be done in a single DELETE statement.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

http://www.lmgtfy.com/?q=oracle+delete+duplicate+rows

[Updated on: Thu, 03 December 2009 09:39]

Report message to a moderator

Re: Easiest way to delete duplicate rows? [message #433697 is a reply to message #433657] Thu, 03 December 2009 12:46 Go to previous messageGo to next message
joy_division
Messages: 4643
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

icon6.gif  Re: Easiest way to delete duplicate rows? [message #433708 is a reply to message #433657] Thu, 03 December 2009 14:28 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
If you want to keep the LATEST inserted/modified row, use this sql:
DELETE FROM   Mytable D
      WHERE   ORA_ROWSCN < (SELECT   MAX (ORA_ROWSCN)
                              FROM   Mytable M
                             WHERE   M.B = D.B)

Shocked
Re: Easiest way to delete duplicate rows? [message #433750 is a reply to message #433708] Fri, 04 December 2009 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Unless you specified "row dependency" on table creation this is wrong and you may keep duplicates after that.
Shocked Shocked Shocked (as you like to have irrelevant smileys in your posts)

Regards
Michel
Re: Easiest way to delete duplicate rows? [message #433829 is a reply to message #433750] Fri, 04 December 2009 07:51 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.

Re: Easiest way to delete duplicate rows? [message #435108 is a reply to message #435104] Mon, 14 December 2009 06:10 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
You will get that from here

http://www.dba-oracle.com/oracle_tips_ora_rowscn_10g_pseudo_column.htm

sriram Smile
Re: Easiest way to delete duplicate rows? [message #435115 is a reply to message #435104] Mon, 14 December 2009 06:43 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is "ORA_ROWSCN" implemented in Oracle 9i ?

No. It was introduced in 10g.

Regards
Michel
Previous Topic: Running OS script file from PL/SQL through java class.
Next Topic: Update multiple rows
Goto Forum:
  


Current Time: Fri Dec 09 09:35:28 CST 2016

Total time taken to generate the page: 0.17670 seconds