Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to clean up duplicate records

Re: How to clean up duplicate records

From: Joe Maloney <mpir_at_bellsouth.net>
Date: 18 May 2001 06:41:05 -0700
Message-ID: <d17bad25.0105180541.131ea13f@posting.google.com>

Two other options:

1- create a clone table

    create clone as
    select distinct f1,f2,f3 from duped
    then either truncate duped and copy the data back

             or drop duped, rename clone and reapply constraints

2- PL/SQL cursor to pass the table in pk order.

       if current pk = prior pk then delete by rowid.

tp601553_at_cia.gov (Tweetie Pooh) wrote in message news:<Xns90A56B9099D92TweetiePooh_at_62.253.162.104>...
> ewong <ewong_at_ewong.com> honoured comp.databases.oracle.server on Fri 18 May
> 2001 05:46:28a with news:3B04A923.A61E93A9_at_ewong.com:
>
> > I have a table (f1 integer, f2 integer, f3 varchar2) with an original
> > composite primary key on (f1, f2). The pk was mistakenly disabled for
> > couple days and I am not able to enable it now because there are couple
> > thousands of duplicate identifical records inserted:
> > f1 f2 f3
> > 1 2 text1
> > 1 2 text1
> > 2 5 text2
> > 2 5 text2
> > ...
> > Is there any kind of sql that can clean it up so that I can enable the
> > pk again? I need to delete one of the two duplicate records.
> >
> > Any help is appreciated.
> >
>
> I've tended to create the unique key and use the EXCEPTIONS INTO clause to
> capture the ROWID's of offending items. Then I can delete those rows from
> the table, then recreate the key.
Received on Fri May 18 2001 - 08:41:05 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US