Re: Q: Oracle sqlplus way to capture all duplications and removal.

From: Albert Minich <Albert_77_at_gmx.net>
Date: Mon, 8 Sep 2003 14:47:34 +0000 (UTC)
Message-ID: <Xns93F0AAD323BE9110oo011@193.96.115.84>

ColoC meinte:

> Hi, is there someone to help me?
>
> I have got a data duplication case in a big Oracle 8 table. Many of
> the records were processed and loaded twice, meanwhile there are much
> more good records without duplication. The table has 60 fields for
> each record.
>
> Is there a simple Oracle sqlplus way to capture all these duplications
> and remove the duplications?
>
> Thanks.
>
> ColoC

Hi ColoC,

you can use self-join:

---

declare
  cursor cur_dup is select a.rowid
    from your_table a, your_table b

    where a.uk_column1 = b.uk_column1
      and a.uk_column2 = b.uk_column2
      and ...
      and a.rowid != b.rowid;

  v_rowid rowid;

begin

  open cur_dup;
    loop

      fetch cur_dup into v_rowid;
      exit when cur_dup%notfound;
      delete from your_table
        where rowid = v_rowid;

    end loop;
  close cur_dup;

end;
/

---

That is realy fast!

HTH
Albert

-- 
Trouble with Windows - Reboot!
Trouble with Linux - Be root!

Received on Mon Sep 08 2003 - 09:47:34 CDT