Re: How to clean up duplicate records

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 - 04:34:27 CDT