Sweet, thanks Tim. I've no worries about figuring out which records to update - that's not a problem. It was *doing* the update without having to loop on X records, doing single updates, that I wanted to get around. And I can't just grease the dupe records and write new ones, as use the IDs on those records elsewhere. I need to update as opposed to delete/insert.
At 02:44 PM 12/2/2002 -0600, you wrote: >The basic bulk update is easy enough: > >Update target > Set Field1=source.Field1, Field2=source.Field2, ... > From MyTargetTable target inner join MySourceTable source on >target.something=source.something > >The trick is going to be in knowing which records you updated, and which >ones need to be inserted. > >You can either delete the data you updated with a delete line using >similar logic above, and then insert the rest > >Delete source > From MyTargetTable target inner join MySourceTable source on >target.something=source.something > >-- and then insert the rest > >Insert into MyTargetTable (Field1, Field2, ...) > Select Field1, Field2, ... > From MySourceTable > Where UpdateInsertFlag is null (or = 0 or whatever) > >Or you can add a column to your source table (I call it UpdateInsertFlag >int in mine) and run a 2nd update: > >Update source > Set UpdateInsertFlag=1 > From MyTargetTable target inner join MySourceTable source on >target.something=source.something > >-- then do an insert for the rest: > >Insert into MyTargetTable (Field1, Field2, ...) > Select Field1, Field2, ... > From MySourceTable > Where UpdateInsertFlag is null (or = 0 or whatever) > >I do the 2nd version in my own scripts, because I'm always afraid of >deleting! ;-) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=6 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=6 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
