Re: Removing dublicate records in table and hostarray

On Fri, 24 Apr 1998 13:29:49 +0200, Brian Rasmusson <br_at_belle.dk> wrote:

<SNIP>
>
>It would be a hassle to clean up the transaction table then..
>

<SNIP>

Why don't you separate the check, the transfer of the valid records and the cleanup in tree separate steps?

Add a column STATUS NUMBER _Nullable_ to your scratchpad table X. Take care that noone but you ever touches it. The filling applications leave this field NULL so unprocessed records can always be recognized.

Now you do the check by updating STATUS

UPDATE X
   SET X.STATUS = ( SELECT COUNT(*) FROM Y WHERE Y.Key_1 = X.Key_1
  AND Y.Key2 = X.Key_2 etc... )
Assuming that you have an index on all relevant fields off course.

After this the rows that have no duplicates have a zero in field STATUS, so for your transfering application you can now select all rows with STATUS = 0. In the mean time those other applications can continue filling table X with more records, because they will be neglected until they in turn have been checked.

When all rows have been transferred you can do a simple cleanup with : DELETE X WHERE STATUS IS NOT NULL; Both the processed rows with STATUS = 0 and the duplicate rows with STATUS = 1 will be deleted by this statement, without ever touching the new rows that might have been inserted in the meantime.

You might even beforehand do a cheap check on your Y table with SELECT COUNT(*) FROM X WHERE STATUS > 1; This should never return anything but zero.

I hope this helps

Henk de Wilde. Received on Sat Apr 25 1998 - 18:01:14 CDT