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! ;-)
-----Original Message-----
From: Scott Weikert [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 02, 2002 2:32 PM
To: SQL
Subject: Semi-bulk updating a table
Hey guys, got a question here.
I've got a table that holds info that our account holders can import
data
into. Right now, it's set to ignore dupes - i.e. I'll filter out any
records from the import (which I bulk insert into a table created for
the
import) that have the same content in a certain field, and then insert
the
non-dupe info, a la
insert into oldtable
select blah blah
from newtable
But now I want to offer the option to either ignore dupes (current
situation) or update dupes. Updating would entail overwriting most, but
not
all, of the fields for any given record in the old table with the
matching
fields' info from the new table.
Is there SQL syntax to do this in bulk, along the lines of the
insert/select above? Or am I stuck doing loops on my data and individual
row updates?
Thanks in advance,
--Scott
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=6
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=6
Get the mailserver that powers this list at http://www.coolfusion.com
