Re: help: delete the duplecated records

what if you use this :

delete from my_table t1
where rowid > (select rowid from my_table t2

                           where t2.my_field=t1.my_field);

sc <cfs3526_at_hotmail.com> schreef in berichtnieuws 971702225.775225_at_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:33:27 CDT