| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: PL/SQL: best way to find and remove duplicate rows ( keep 1 row )
Try this:
Let the primary keys(or duplicate keys) be col1, col2 of table1
delete from table1 A
where A.rowid > (select min(rowid) from table1 B
where A.col1 = B.col1
and A.col2= B.col2)
Alternatively,
delete from table1 A
where A.rowid < (select max(rowid) from table1 B
where A.col1 = B.col1
and A.col2= B.col2)
Chandu
delete from table
In article <7onh7g$1gu$1_at_ash.prod.itd.earthlink.net>,
"Paul Chu" <chupaul_at_earthlink.net> wrote:
> Thomas and Ken,
>
> Thanks so much for replying.
>
> I took your fancy correlated subquery concept and applied it to
> also show all duplicates in a table first.
>
> create table my_table1
> ( my_table char(30), my_count integer );
>
> -- Remove duplicates keeping one dup with max(rowid)
> delete from my_table1 t
> where rowid <> ( select max(rowid)
> from my_table1 t2
> where t2.my_table = t.my_table );
>
> -- Find and Display Each duplicate row
> select a.*, rowid from my_table1 a
> where a.my_table = ( select b.my_table from
> my_table1 b
> where b.my_table = a.my_table
> group by b.my_table
> having count(*) > 1 )
> order by my_table;
>
> Regards, Paul
>
> Thomas Kyte <tkyte_at_us.oracle.com> wrote in message
> news:37aec58e.869900_at_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
>
>
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
Received on Mon Aug 09 1999 - 21:54:03 CDT
 |
 |