Re: Need alternate way of eliminating duplicates from 93K table

In article <4kc15f$30j_at_mips.infocom.com>, bobwal_at_infocom.com says...
>
>jp_at_vllyoak.resun.com (Jeff Perry) wrote:
>> ...
>> If you know of some alternate method of removing these
>> duplicate records, please share with me your ideas.
>
>declare
>
> cursor dups is
> select key1, key2, key3, key4, . . . .
> from table_name
> group by key1, key2, key3, key4, . . . .
> having count(*) >= 2;
>
> save_doops dups%rowtype;
>
> any_rowid varchar2(100);
>
>begin
> for doops in dups loop
> begin
> if save_doops is null or
> doops <> save_doops then
> save_doops := doops;
> select rowid
> into any_rowid
> from table_name
> where doops.key1 = key1
> and doops.key2 = key2
> and doops.key3 = key3
> and doops.key4 = key4 ....
> end if;
> exception
> when too_many_rows then
> delete from table_name where rowid = any_rowid;
> commit;
> end;
> end loop;
>end;
>
>Other than the inadvertant syntax and declaration errors, this should
>delete ONE of the duplicate rows in the table. If there are more than
>2 duplicates, just run it again.

the following should delete all duplicates in one pass

declare
  cursor all_rows is
  select key1, key2, key3, key4, . . . .   from table_name
  order by key1, key2, key3, key4, . . . .

  last_key all_rows%rowtype;
begin
  for current_row in all_rows loop
    begin

      if ( last_key is null                  ) or
         ( last_key.key1 <> current_row.key1 ) or
         ( last_key.key2 <> current_row.key2 ) or
       .
       .
       .
      then
         last_key = current_row ;
      else
         delete where current of all_rows ;
      end if;

hth

-- 
    _/_/_/   _/_/_/ _/    _/  // Reinhard Kuhn             /  It can be      
   _/    _/ _/     _/  _/    //         (kuhn_at_cas-ps.com) /  done quickly,   
  _/_/_/   _/_/_/ _/_/      // CAS GmbH                  /  cheaply or well  
 _/  _/   _/     _/  _/    // Lemberger Strasse 14      /   - pick any two!  
_/   _/  _/_/_/ _/    _/  // 66955 Pirmasens, Germany  /