Is there an equivalent of the MERGE SQL statement in MS Acess

I want to add a row for a table if the key doesnt exist but update a field if the key does exist. This is accomplished easil using MERGE in Oracle, but I dont think this clause exists in Access ...?
fester62DeveloperAsked:
Who is Participating?
 
David ToddConnect With a Mentor Senior DBACommented:
Cool. Looks like you've got it.

Cheers
  David
0
 
David ToddSenior DBACommented:
Hi,

Merge is coming to SQL2008, but I havent heard if Access is getting it.

The usual way around is something like this
create temp table
insert into temp table

insert into finaltable
select
from temptable
left outer join finaltable
  on finaltable.field = temptable.field
where finaltable.field is null

update ft
  set ft.field = tt.field
from finaltable ft
inner join temptable tt
  on finaltable.field = temptable.field
where
  ft.field != tt.field

HTH
  David


0
 
fester62DeveloperAuthor Commented:
Thanks I can see how that would work, For a 3-field key, I assume I could modify as follows:

insert into finaltable
select
from temptable
left outer join finaltable
  on (finaltable.field1 = temptable.field1
  and finaltable.field1 = temptable.field2
  and finaltable.field3 = temptable.field3)
where finaltable.field1  is null
  and finaltable.field2 is null
  and finaltable.field3 is null;

update ft
  set ft.field4 = tt.field4
from finaltable ft
inner join temptable tt
  on finaltable.field1 = temptable.field1
   and finaltable.field2 = temptable.field2
  and finaltable.field3 = temptable.field3
where
  ft.field4 != tt.field4

Appreciate your help. Thanks again.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Gustav BrockCIOCommented:
Actually, it is quite easy in Access and any other SQL engine.

Create a table with your updates (here tblEEfficiencyTMP) and a query like this to update/append your main table (here tblEEfficiency). Unique key is field ID here:

UPDATE tblPEEfficiency
  RIGHT JOIN tblPEEfficiencyTMP ON
    (tblPEEfficiency.ID = tblPEEfficiencyTMP.ID)
SET
  tblPEEfficiency.ID = tblPEEfficiencyTMP.ID,
  tblPEEfficiency.Date = tblPEEfficiencyTMP.Date,
  tblPEEfficiency.BOPS = tblPEEfficiencyTMP.BOPS;

Works like magic!

/gustav
0
 
fester62DeveloperAuthor Commented:
Surely this only does the updates, it is not appending any records to tblEEfficiency ? I cant see how you can do it without both an INSERT and an UPDATE.
Oh I see, hold on ....the nulls on the main table would get updated to the missing values and effectively perform an INSERT.

I'll have to give that a try ...very cool if it works as you are only using one statement. Thanks gustav.

Tony
0
 
Gustav BrockCIOCommented:
You are welcome!

I have used it several times and it works. Well, a bit like magic.

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.