Re: Deleting duplicates

delete from table_1 a
where a.rowid >

   (select min(b.rowid)
    from table_1 b
    where b.col_dup_values = a.col_dup_values)

Enjoy!

Steve
804-262-6332

In <4qbcri$9a9_at_lserv1.paging.mot.com> vgoel_at_pts.mot.com (Vikram Goel (Cntr)) writes:
>
>Duane,
>
>An easy way to select duplicates is the following:
>1) Determine the uniqueness of each row, by a column or columns.
>2) Then try to build a primary key on the column(s) with the
 exceptions into
> <EXCEPTIONS> clause. The creation of the special EXCEPTIONS table
 is in the
> utlexpt.sql script in $ORACLE_HOME/rdbms/admin. e.g.
> alter table <table_name> add constraunt <index_name>
 (<column_list>
> using index ....
> exceptions into exceptions;
>3) The exceptions table holds the rowid of the duplicate row(s). NOTE:
 If there are
> two rows only one of rows rowid, if three only two etc.
>4) Use the value of the rowid in the exceptions table to delete the
 rows.
>
>This is the fastest method to delete dups, as deletes are done using
 the rowid.
>
>Hope this helps,
>
>--
>Vikram Goel
>Sr. Oracle DBA - Consultant
>Aerotek Inc. My email: vgoel_at_emi.net
>
>Motorola Info: Motorola email:
 vgoel_at_pts.mot.com
>Mail Stop 39, Room S1014
>1500 Gateway Blvd,
>Boynton Beach, FL 33426
>
>
>In article <4q91mk$c07_at_srvr1.engin.umich.edu>, duanef_at_umich.edu (Any
 One) writes:
>>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.
>>
>>Thanks,
>>duanef_at_umich.edu
>>
>
>
>
Received on Sat Jun 22 1996 - 00:00:00 CEST