Re: Duplicate records problem
Date: 1996/03/04
Message-ID: <4hfinj$s51_at_news.Belgium.EU.net>#1/1
In article <DnLopL.86J_at_ulysses.homer.att.com>, raj_at_ulysses.att.com
says...
!>
!>I am trying to insert records into a table using PL/SQL.
!>There are duplicate records and I am trying to insert only
!>one of the duplicate records.
!>
!>One of the options I know would be to do a select distinct
!>and then insert. But this takes a very long time.
!>
!>Is there a way to insert only one of the duplicate
!>records? I tried adding a unique key index but this does not
!>allow insertion of rows that violate the unique key index.
!>
!>The version of Oracle is 7.2
!>running on Unix (SunOS)
!>
!>Request your help.
!>
!>Thanks
!>
!>Rajeev
!>
If you are using PL/SQL, why not select the records ordered, compare each
fetched record to the previous one, and insert it only if it differs?
declare
prevrec itable%ROWTYPE;
for irec in (select * from itable order by field_a, field_b, ... ) loop
if prevrec.field_a <> irec.field_a
or prevrec.field_b <> irec.field_b
or ... then /* if one of the fields differs;
insert into otable values (irec.field_a, irec.field_b, ...);
prevrec.field_a := irec.field_a;
prevrec.field_b := irec.field_b;
... /* save all fields that have to be compared */
end loop;
Received on Mon Mar 04 1996 - 00:00:00 CET