Re: Remove duplicate data in Oracle DB

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 15 2000 - 00:00:00 CST