By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,995 Members | 1,090 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,995 IT Pros & Developers. It's quick & easy.

How to do an update on existing records?

P: n/a
I have one table of new records (tableA) that may already exist in
tableB. I want to insert these records into tableB with insert if they
don't already exist, or update any existing ones with new data if they
do already exist. A column (Action) in tableA already tells me whether
this is an INSERT, UPDATE, or DELETE. I'm able to derive that I can do
an insert with

select * into tableB from tableA where Action = 'INSERT'

....and I think I can handle the delete.

But I'm stuck on the update. How do I do the update? An ordinary
UPDATE statement just won't do unless I use a cursor to cycle through
the recordset. I want to avoid a cursor.
Jul 20 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a

"Google Mike" <go********@hotpop.com> wrote in message
news:25**************************@posting.google.c om...
I have one table of new records (tableA) that may already exist in
tableB. I want to insert these records into tableB with insert if they
don't already exist, or update any existing ones with new data if they
do already exist. A column (Action) in tableA already tells me whether
this is an INSERT, UPDATE, or DELETE. I'm able to derive that I can do
an insert with

select * into tableB from tableA where Action = 'INSERT'

...and I think I can handle the delete.

But I'm stuck on the update. How do I do the update? An ordinary
UPDATE statement just won't do unless I use a cursor to cycle through
the recordset. I want to avoid a cursor.


I don't completely understand your description, and it would be useful to
see the structure of your tables (ie CREATE TABLE statements), as well as
some sample data. However, here is a fairly generic solution - if it doesn't
work as you expect, then please consider posting the additional information.

/* INSERT new records */

insert into dbo.tableB (col1, col2, ...)
select col1, col2, ...
from dbo.tableA a
where not exists
(select * from dbo.tableB b
where a.PrimaryKeyCol = b.PrimaryKeyCol)
and a.[Action] = 'INSERT'

/* UPDATE existing records */

update dbo.tableB
set col1 = a.col1, col2 = a.col2, ...
from dbo.tableB b
join dbo.tableA a
on a.PrimaryKeyCol = b.PrimaryKeyCol
where a.[Action] = 'UPDATE'

/* DELETE existing records */

delete from dbo.tableB
where exists
(select * from dbo.tableA a
where a.PrimaryKeyCol = dbo.tableB.PrimaryKeyCol
and a.[Action] = 'DELETE')

Note that 'Action' is listed in "Reserved Keywords" as a word to avoid using
in code (at least in SQL 2000 Books Online - you didn't mention which
version of MSSQL you're using).

Simon
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.