Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Removing dublicate records in table and hostarray
Hi,
> sss see if it is helpful to you. Query based ons single field ids
> (replace this with the original fields on which
> sss duplicate records have come. )
> sss Can you do like below and put the result in hostarray instead ofs
> processing hostarrays to mark the record
> sss whether it is duplicate or not ?
It would be a hassle to clean up the transaction table then..
Here is my detailed scenario:
I read 1000 records from table X into my hostarray. I want to find out which records in the hostarray already exists in the destination table Y.
What i would like is a sql statement that can return a list of statuses,
one for each record on my hostarray.
I would do something like this:
col1[1000]
col2[1000]
status[1000]
col1+2 is filled by my select from table x
status is filled by an examination in table y which records from my
hostarray already exists.
I imagine a statement returning a 0 if the record is not a dupe, and 1
if it is. I can then insert all records with status 0, and know it wont
failed because of the primary key constraint.
I continue the steps above, until table X is empty. It may contain 100.000's of records, and the destination table contains millions of records, so there is no way i can have all records in memory at the same time. Thats why i read them in chunks of 1000 records.
Table X is filled by process A, while process B reads data from it. This cannot be synchronized.
Thanks,
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 - 06:29:49 CDT