Re: DELETING DUPLICATE ROWS

smantri_at_site.gmu.edu (Shiva K Mantri (CS)) wrote:

>Hi Everybody,
>
>Can any one tell me how to delete duplicate rows in a table?
>
> 1) the rows are duplicate by primary key.
>
>I need to delete all the rows identical row values
>
>------Shiva Mantri

Do you want to leave one of the rows intact and just delete the duplicates? Below is a script I use to do that. I not only deletes duplicates, but leaves an audit trail of the duplicate pk_values.

accept tabnam prompt 'Enter table name to dedupe: ' accept pk_cols prompt 'Enter primary key 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$) /

--
Chuck Hamilton
chuckh_at_dvol.com

Never share a foxhole with anyone braver than yourself!