Re: How to clean up duplicate records

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Fri, 18 May 2001 06:59:17 +0200
Message-ID: <tg9at99nbg9554@beta-news.demon.nl>

"ewong" <ewong_at_ewong.com> wrote in message 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.
>

Of course there is and the code has been posted here numerous times.

the general idea is

delete from foo x
where x.rowid <> (select min(rowid) from foo y

                              where y.primary key = x.primary key)

I leave the rest for you as an exercise.

Hth,

Sybrand Bakker, Oracle DBA Received on Thu May 17 2001 - 23:59:17 CDT