Re: help:delete duplicated records

There are lots of ways.

Here's one I sometimes use when large number of dupes is involved -- it uses a small rollback.

Assuming table 'my_table' with field 'my_field' where 'my_field' is duped and cannot be used as the primary key:

Create a table containing (only) the distinct 'my_fields' which are duped:

create table dupes as
select my_field from my_table
group by my_field
having count(*) > 1
/

Then delete rows from 'my_table' that are duped using the strategy of keeping the first of a duped set of rows.

create or replace procedure de_dupe_my_table as

--
-- read thru DUPES joined with MY_TABLE to
-- collect all duplicate rows.  Keep the first of
-- a 'dupe set', deleting the 2nd, 3rd, etc.
--
  cursor c1 is
    select my_table.rowid my_row_id, my_table.my_field
    from dupes, my_table where dupes.my_field = my_table.my_field
    order by my_table.my_field;
--
  my_keep_field    my_table.my_field%TYPE;
--
  begin
      my_keep_field := null;
--
      for c in c1 loop
         if c.my_field <> my_keep_field then
             /* this is the first of a dupe set */
             my_keep_fl := c.my_field;
         else
             /* we're deleting 2nd, 3rd, ... dupe rows */
             begin
               delete from my_table where rowid = c.my_row_id;
               commit;
               exceptions
                   when others then null;
             end;
      end loop;
  end;




"sc" <cfs3526_at_hotmail.com> wrote in message
news:971115726.509012_at_newsreader-hpw1.net.bms.com...


> I have a table which has about 20,000 duplicated rows.  When I was trying

 to


> delete it using rowid, it takes for ever to finish, finally I have to

 cancel


> the query.  Could somebody tell me how I should do with it?  I can not

> enable PK now for the table.

>

> Thank  you for you help.

>

>