Re: Eliminating Dup Records

Martyn Richard Jones wrote:
>
> > In article <31EE6504.667F_at_CAMBER.COM>, M Rajeshwar <MR1154_at_CAMBER.COM> says:
> > >
> > >Since the primary key constraints were disabled, one of my tables now is
> > >flooded with duplicate records. I had known a method of maintaing
> > >unique records, deleting duplicate records, but not able to recall it.
> > >
> > >Can anyone help me out in this regard ?
> > >
> > >Thanks
> > >
> > >Raj
> >
> > It's a long time since I did thos one, but you may care
> > to try the following (test it first).
> > OOOOPS!
>
> DELETE FROM TABLE my_table
> WHERE primary_key =
> (SELECT a.primary_key
> FROM my_Table a, my_Table b
> WHERE a.primary_key = b.primary_key
> AND b.row_id > a.row_id)
>
> There, that's better.
>
> > Martyn Jones
> > Iniciativa para Data Warehouse
> > San Lorenzod de El Escorial

The example above would work if you assume that the entire record is a duplicate, and that it is OK to delete either one of them. If you are not sure that that is the case, you need to identify the duplicate records, then review them to determine which one you can delete.

You can identify them pretty easily. Reviewing them will be a pain if you have a lot of them.

Use the EXCEPTIONS clause of your primary key constraint to put the rowids of the duplicate rows into the exception table. Then use those rowids to select the duplicate rows from the original table.

rem
rem Build the exceptions table
rem
_at_$ORACLE_HOME/rdbms/admin/utlexcpt.sql rem
rem Try to enable the constraint. It will fail, but will put the rem rowids of the duplicate rows into the EXCEPTIONS table. rem
alter table ABC add constraint ABC_PK primary key (KEY_COLUMN)

      exceptions into EXCEPTIONS;
rem
rem Now display the duplicate rows.
rem
select a.rowid, a.*
  from ABC A, exceptions x
  where a.rowid = x.row_id;

Pick the rows to delete and delete them by their rowid.  

-- 
Bob Swisshelm
Eli Lilly and Company
swisshelm_at_lilly.com