473,230 Members | 1,448 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,230 software developers and data experts.

How to do an update on existing records?

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
1 3660

"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

16
by: Philip Boonzaaier | last post by:
I want to be able to generate SQL statements that will go through a list of data, effectively row by row, enquire on the database if this exists in the selected table- If it exists, then the colums...
2
by: Doug Bell | last post by:
Hi I have an application where I need to Update an XML file. The source is an Access DB. The Application imports new records from the Access DB into a DataSet and then needs to update the XML...
4
by: Mike Dole | last post by:
I might have taken a wrong turn trying to do an update on a database the way I did but maybe somebody can help me out cause I'm stuck.. I have to update 12 tables in a database in a client...
8
by: Maxi | last post by:
There is a lotto system which picks 21 numbers every day out of 80 numbers. I have a table (name:Lotto) with 22 fields (name:Date,P1,P2....P21) Here is the structure and sample data: ...
7
by: PC Datasheet | last post by:
Looking for suggestions ---- A database was designed for a national automobile inspection program. In it's simplest form, the database has two tables: TblOwner OwnerID <Year/Make/Model owned...
0
by: M. David Johnson | last post by:
I cannot get my OleDbDataAdapter to update my database table from my local dataset table. The Knowledge Base doesn't seem to help - see item 10 below. I have a Microsoft Access 2000 database...
3
by: Roy | last post by:
Hi Access gurus, I have a A2K application.The data in the database is updated daily by a excel download.I have a master n related tables keyed in by a OrderID.I have a problem in updating data.If...
4
by: MN | last post by:
I have to import a tab-delimited text file daily into Access through a macro. All of the data needs to be added to an existing table. Some of the data already exists but may be updated by the...
2
by: BobLewiston | last post by:
Some of you may have seen my earlier thread “PasswordHash NULL problem”. I’ve started a new thread because investigation has shown that the problem is actually quite different than I previously...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.