help: delete the duplecated records

From: sc <cfs3526_at_hotmail.com>
Date: Mon, 16 Oct 2000 09:17:03 -0400
Message-ID: <971702225.775225@newsreader-hpw1.net.bms.com>

I have a table with about 10,000 duplecated records. When I tried to use rowid to delete them, it takes a long long time to finish, and I have to cancel the query. The some body send me a procedure as follows,

---

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;

but it delete all the duplecated records instead of keeping the first one, could somebody help me out?

Thank you very much. Received on Mon Oct 16 2000 - 08:17:03 CDT