Re: Removing dublicate records in table and hostarray

From: T Suresh kumar <sureshkt_at_hotmail.com>
Date: Fri, 24 Apr 1998 15:01:49 +0530
Message-ID: <35405C04.598F6EA7@hotmail.com>

Hai

    see if it is helpful to you. Query based on single field id (replace this with the original fields on which

    duplicate records have come. )
    Can you do like below and put the result in hostarray instead of processing hostarray to mark the record

    whether it is duplicate or not ?

   select a.id , a.dupl
   from (select id, 0 dupl

               from     t1
               where rowid in (select max(rowid) from t1 group by id)
               group by id
                union all
                select id, 1 dupl
                from    t1
                where rowid not in(select max(rowid) from t1 group by

id) a
order by a.id
 /
Regards
Suresh

Brian Rasmusson wrote:

> Hi,
>
> I have a tricky problem that i would like to discuss.
>
> Imagine this scenario:
>
> Table X contains transactions, and may contain dublicates.
> Table Y contains the processed transactions, and no dublicates may
> exists. This is handled by having a primary key that the raw
> transactions does not have.
>
> Several machines deliver transactions to the master, placing them in
> table X. My application reads the transactions, and inserts them into
> the destination table Y after processing. If an insert of one
> transaction fail, i simply roll back.
>
> Now, i would like to optimize this. I now read 1000 transactions into
> a
> hostarray. I now want to do something like a FOR
> :number_of_records_in_hostarray delete from table Y where keycol1=:k1
> and keycol2=:k2 so i _know_ that i can insert the remaining records in
>
> one large bulk insert.
>
> Any suggestions on how to do this?
>
> Thanks in advance,
> Brian
>
> -----
> ------------------------------------------------------------------
> Brian Rasmusson, Software engineer & analyst e-mail:
> br_at_belle.dk
> Belle Systems A/S web :
> www.belle.dk
> Network, Internet and communication specialists phone : +45
> 59442500
> --------
> ---------------------------------------------------------------
Received on Fri Apr 24 1998 - 04:31:49 CDT