>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
Jason,
At least to me it's hard to understand. Could you elaborate it more with sampling + version. ie: I couldn't get your 2nd paragraph "This table has a unique...".
Sounds like you're doing "SQL delete then insert" in a different way.
Cetin