Re: Remove duplicate data in Oracle DB

Or, you may use the delete like this:

delete from T
 where rowid not in (select max(rowid)

                               from T
                              group by pk);

or if you don't want to use NOT IN, try

delete from T
 where not exists (select 1

                            from (select max(rowid) maxRow
                                      from T
                                     group by pk) T1
                            where T1.maxRow = T.rowid);

If your Table is too large, you may add one more condition like:

and pk in (select pk

                 from T
                group by pk
                 having count(*) >= 2)

into your delete statement.

Good luck!

rporazil_at_my-deja.com wrote:

> In case the duplications are rare and there are a
> couple of columns-candidates for primary key, you
> may identify duplication by select like this
>
> select col1, col2, col3, count(*)
> from tab
> group by col1 col2 col3
> having count(*) > 1;
>
> then select all rows from each group
> select *
> from tab
> where col1=c1 and col2=c2 and col3=c3;
> decide which to throw away and delete them (you
> may select rowid for them).
> In case it dosen't matter which to delete, try
> for example
>
> delete from tab
> where col1=c1 and col2=c2 and col3=c3
> and rownum < <count(*) of the group>
>
> Not very smart, but it works.
>
> R.
> In article <38A8FEA1.35D33056_at_email.sps.mot.com>,
> N.Hadi_at_motorola.com wrote:
> > I have tables in Oracle database which I could
> not enforce primary key
> > due to unavoidable situation. Because of this,
> I might see duplications
> > of data row.
> >
> > I want to know if it is possible for me to
> remove the duplicate row(s)?
> > Appreciate any reply, reference or any tool
> that I could use to overcome
> > this situation.
> >
> > Thanks in advance.
> >
> > Best Regards,
> > hadi
> >
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Tue Feb 22 2000 - 08:27:06 CST