Re: Duplicate records problem

From: Gerard H. Pille <ghp_at_infosoft.be>
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

Original text of this message