Re: PL/SQL: best way to find and remove duplicate rows ( keep 1 row )

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 09 Aug 1999 12:16:50 GMT
Message-ID: <37aec58e.869900@newshost.us.oracle.com>

A copy of this was sent to Kenneth C Stahl <BluesSax_at_Unforgettable.com> (if that email address didn't require changing) On Mon, 09 Aug 1999 07:46:20 -0400, you wrote:

>Declare
> Cursor C1 is
> select distinct key,rowid
> from mytable;
>Begin
> for i in C1 loop
> delete mytable
> where key = i.key
> and rowid != i.rowid;
> End loop
>End;
>

that deletes ALL duplicates - it does not leave one behind.

consider:

SQL> create table t ( x int );
Table created.

SQL> insert into t values ( 1 );
SQL> insert into t values ( 1 );
SQL> insert into t values ( 2 );
SQL> commit;

Commit complete.

SQL> Declare

  2      Cursor C1 is
  3      select distinct x,rowid
  4       from T;
  5  Begin
  6      for i in C1 loop
  7          delete from t
  8          where x = i.x
  9               and rowid != i.rowid;
 10      End loop;

 11 End;
 12 /

PL/SQL procedure successfully completed.

SQL>
SQL> select * from t;

         X

---

         2

You don't need plsql to do this, it would just be something like:

SQL> delete from T
  2 where rowid <> ( select max(rowid)

  3                      from T t2
  4                     where t2.x = t.x )

  5 /

1 row deleted.

SQL> select * from t;

         X

---

         1
         2

that picks a 'random' duplicate (the one with the max rowid) to keep and deletes the rest of them.

>Paul Chu wrote:
>
>> Hi all,
>>
>> I would like a script which would find all the duplicate rows in the table
>> and keep 1 of the duplicate rows and delete the others.
>>
>> Any suggestions.
>>
>> Regards, Paul

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Aug 09 1999 - 07:16:50 CDT