I have a little table that holds about a million records which is a link between two other tables.

This table has a unique key which must be constant until it is deleted. Therefore when an entry is changed, that row is deleted and a new row is created.

The process is:
build a new table
compare the existing table against the new table
delete old entries
create new entries

Please not that this is field by field comparision as a change any field would result in deleting the old record and creating a new..

Therfore, and old list and new list is created by bring all fields into one with a select field1 + field2 + fieldn for each row. The old records are found by:

select key ;
from oldlist ;
where expr not in (select expr from newlist)

And the same happens with for new records. The keys are then use to delete the old and create the new in the actual table. Now wait for it... I stopped the processing after an hour (P166 32MB RAM).

Does anyone have a better method of perfing the same task.

I look forward to hearing some great suggestions..

Jason