Re: Deleting duplicates

Loh Buck Cheng <buckloh_at_po.pacific.net.sg> wrote:

>duanef_at_umich.edu (Any One) wrote:
>> gave any one give me some sample code to delete duplicates? I have a
>> table that now contains duplicate data (due to unforseen circumstances)
>> that I need to clean up.
>>
>
>Try delete ORDER_HEADER x
> where exists (select 'x'
> from ORDER_HEADER y
> where x.order_no = y.order_no
> and x.rowid <> y.rowid
> )
>
>Rgds,
>Buck

Have you actually test this? When I tried it, it deletes _all_ the duplicates. The first dupe deletes the 2nd, the 2nd deletes the 1st. Normally you want to leave one of them alone.

The script I use to dedupe a table looks like this. It assumes you're using the PK column(s) identify duplicates:

accept tabnam prompt 'Enter table name to dedupe: ' accept pk_cols prompt 'Enter PK columns separated by commas: '  

set verify off  

drop table dedupe$
/
create table dedupe$
as (select '&&tabnam' table_name, &&pk_cols, min(rowid) min_rowid

    from &&tabnam
    group by &&pk_cols
    having count(*) > 1)
/
delete from &&tabnam
where (&&pk_cols) in (select &&pk_cols from dedupe$) and rowid not in (select min_rowid from dedupe$) /

undefine tabnam
undefine pk_cols

--
Chuck Hamilton
chuckh_at_dvol.com

Never share a foxhole with anyone braver than yourself!