OraFAQ Forum: SQL & PL/SQL » Delete Duplicates

Home » SQL & PL/SQL » SQL & PL/SQL » Delete Duplicates

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Delete Duplicates [message #998]

Tue, 19 March 2002 12:45

CJ
Messages: 8
Registered: September 2000

Junior Member

I have a table that has 5 columns. Columns A,B,C,and D need to be unique - duplicates occur when column E has different data. Is there a way to delete the duplicates - using a column F? Column F contains dates - We would need to delete all duplicate records where A,B,C,and D are equal and column F has the earliest date range. For Example:

005 OR 12345 123 description 1/1/92
005 OR 12345 123 Diff Desc. 12/1/01

I would want the first record deleted, but keep the second. It is not a static thing - the dates and the data will differ greatly. There are roughly 300,000 records to manually go through, so any help is GREATLY appreciated!

Report message to a moderator

Re: Delete Duplicates [message #999 is a reply to message #998]

Tue, 19 March 2002 14:36

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

You might try something like:

delete
  from t 
 where (a, b, c, d, f) in
   (select a, b, c, d, min(f)
      from t
     where (a, b, c, d) in
       (select a, b, c, d
          from t
         group by a, b, c, d
         having count(*) > 1)
     group by a, b, c, d);

This is basically saying "Find all the the duplicate a/b/c/d values and then delete the row that has the earliest f (date) value among them."

Report message to a moderator

Re: Delete Duplicates [message #1004 is a reply to message #998]

Wed, 20 March 2002 05:55

CJ
Messages: 8
Registered: September 2000

Junior Member

Thanks for the help!

This is the query I used, but I still keep getting errors and I can't figure out why.... The errors are below the query

delete from mc_cptcodes_dup
where (Machine, Data_Area, CPTCode_Base, CPTCode_Extension) in
(select Machine, Data_Area, CPTCode_Base, CPTCode_Extension, min(CPT_FROM_DATE)
from mc_cptcodes_dup
where (Machine, Data_Area, CPTCode_Base, CPTCode_Extension) in mc_cptcodes_dup
(select Machine, Data_Area, CPTCode_Base, CPTCode_Extension
from mc_cptcodes_dup
group by Machine, Data_Area, CPTCode_Base, CPTCode_Extension
having count(*) > 1)
group by Machine, Data_Area, CPTCode_Base, CPTCode_Extension)

******************************
ERROR:::::

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ','.
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near ','.
Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'group'.

Report message to a moderator

Previous Topic:	Re: Cross Tab Report
Next Topic:	Oracle SQL*Plus and Microsoft Access

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 14:29:43 CDT 2024