Re: Removing duplicate rows from a table !

We just discussed this about a month ago.

3 basic solutions:

1. delete from table where rowid not in (select max(rowid) from table group by col_1,col_2,etc); sql only solution, not really feasible in huge environments
2. Alter table mytab enable constraint PK exceptions into exceptions; Better way; much faster for large tables, lets you audit the duplicate rows by examining exceptions table.
3. Write a cursor; sql coding solution ... probably doesn't give you anything mroe than what option 2 provides.

boss

>
> Hi,
>
> I am trying to remove the duplicate rows from a table with the column data..
> I cannot use PK as it's just a sequence number...
>
> I could find all the duplicate rows by grouping the column. but how can i delete only the duplicate ones and retain the original data..
>
> Any help is gr8 ! ! !
>
> Warm Regards
> Shreekanth
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Rama, Shreekantha (K.)
> INET: srama2_at_ford.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Todd Boss
  INET: boss_at_i-sphere.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).