Re: The best update strategy ? ;)

jparker@epsilon.com wrote: > I have a personal favourite method that takes advantage of the engines > ability to read/write quickly and avoids the slow update altogether: > > Old_table = table to be updated > upd_table = table to update from > > 1) select a.*, b.some_non_null_column > from old_table a, outer upd_table b > into temp t1 with no log > > {we've now built a dataset with a flag (some_non_null_column) which > says whether the row is to be 'updated' or not. } > > 2) create new_table (same as old_table) > > 3) insert into new_table > select * from t1 where some_non_null_column IS NULL > {i.e. where not updated - all the old data}> > 4) insert into new_table select * from upd_table > {all of the update rows - this particular case assumes that you have > an exact image of the old_table record in upd_table - whatever you need to > do...} > > 5) drop old_table > 6) rename new_table to old_table > > With me so far? Now replace steps 3 and 4 with HPL jobs (unless you're > using XPS). This method can scream. Jake its nice method when a. No referential integrity is used b. The amount of "updated" values is much bigger then amount of "not for update" values. Anyway, thanks you for the answer !. Sincerely, Alexander -- "People will work eight hours a day for pay, 10 hours a day for a good boss, and 24 hours a day for a good cause!" - John C. Maxwell