Can I use the SQL MERGE statement in a query in Access 2010

Like many people I have tables I need to add rows to and/or update rows. Instead of using 2 queries to do both is the MERGE statement available in Access 2010 (query)? When I try to run the code it tells me
"Invalid SQL statement; expected 'DELETE','INSERT','PROCEDURE','SELECT', or "UPDATE'" and when I click OK it hilights the word MERGE.
I thought MERGE was Ansi SQL. I may be wrong. If not allowed is there a work around?

Thanks
Chuck LoweAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
JET/ACE SQL does not implement all ANSI SQL levels..

So UPDATE the existing rows first, then INSERT the missing ones. The order is important to reduce the rows touched.
Rey Obrero (Capricorn1)Commented:
yes, perhaps there is a work around if you can describe in details what your two queries intends to do.

post the sql of the query..

maybe a union query can do what you want to do.
Rey Obrero (Capricorn1)Commented:
actually, you can do an Update and Append query in a single statement(query)

something like this

update a
left join b on a.id=bid
set a.f1=b.f1
, a.f2=b.f2
, a.f3=b.f3

it will update table a and will append records that are in b and not in a
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Chuck LoweAuthor Commented:
The source table, tbl_Clients, has a unique record on the key [Plan ID]. The target table, tbl_Plans, has a unique record on the key PlanID. I want to update all fields in the target table if I find a match (except of course for the unique key). If not match is found I want to add the record. I do not want to delete any target records. At least not in this query.

PARAMETERS txtUserID Text ( 255 );
MERGE tbl_Plans AS P
USING tbl_Clients AS C
      ON (P.PlanID = C.[Plan ID])
WHEN NOT MATCHED BY TARGET
 THEN INSERT(PlanID, PlanName, PlanStatus, PrototypeInd, ClientID, ClientName, ParentCompany, UserID, UpdateTS)
VALUES (C.[Plan ID], C.[Plan Name], C.Status, IIf C.([VGI Prototype]="Y",True,False) , Nz(C.([Vision Client Id])) AS ClientID,
C.[Company Name], C.[Parent Company], UCASE([txtUserID]) AS UserID, Now()
WHEN MATCHED
     THEN UPDATE SET P.PlanName= C.[Plan Name],P.PlanStatus = C.Status,P.PrototypeInd = IIf C.([VGI Prototype]="Y",True,False) ,
P.ClientId = Nz(C.([Vision Client Id])), P.ClientName = C.[Company Name], P.ParentCompany = C.[Parent Company],P.UserID = UCASE([txtUserID]),
P.UpdateTs = Now()
OUTPUT $action, Inserted.*, Deleted.*;
Dale FyeOwner, Developing Solutions LLCCommented:
actually, the "MERGE" syntax will not work, but Access has the ability to perform an "UPSERT" query, which performs Update and Inserts in a single step.

To do this:
1. make a backup of your database
2. add the table you want to insert/update from to your query grid (TableA)
3. add the table that you want to insert into / update to your query grid (TableB)
4. add LEFT JOIN(s) from the updating table (TableA) to updated table (TableB)
5. add all of the fields from the table being updated (TableB) to the grid
6. change the query to an Update query
7. In the UpdateTo row, for each column, enter the the appropriate field name associated with TableA

The SQL syntax will look something like:

UPDATE TableA LEFT JOIN tableB
ON tableA.lngNumber = tableB.lngNumber
SET tableB.lngNumber = [tableA].[lngNumber]
, tableB.TestDate = [tableA].[TestDate];

This seems a bit confusing having the update say "UPDATE TableA"  but it is the SET clause that takes care of this for you.
Dale FyeOwner, Developing Solutions LLCCommented:
Rey's syntax is incorrect.  Note that he LEFT JOINed A to B, but then updates the value of A from B.  Because of this, records in B but not A will not be added to A.
Rey Obrero (Capricorn1)Commented:
<Rey's syntax is incorrect.  Note that he LEFT JOINed A to B, but then updates the value of A from B.  Because of this, records in B but not A will not be added to A. >

better test it first , before posting your comment
Rey Obrero (Capricorn1)Commented:
@dale

yes you are correct, it was typo

update b
left join a on b.id=a.id
set a.f1=b.f1
, a.f2=b.f2
, a.f3=b.f3
Chuck LoweAuthor Commented:
@Dale
First thanks for the help. Also to Rey for the help.

I coded as you stated. The target table I'm updating (tbl_Plans AS P) has 21 rows in it. The source table I'm getting the data from (Clients AS C) has 4,854 rows in it. When I run the query I get the message "You are about to Update 4,854 row(s)" I click on Yes to continue. I get a message that "Microsoft Access can't update all the records in the update query"  The only violation is 21 record(s) due to key violations. It did add 4,833 rows but did not update the 21 rows that are currently on the database. Here is the exact query.
(I apoligize for not attaching a file but we are not allowed)

PARAMETERS txtUserID Text ( 255 );
UPDATE Clients AS C LEFT JOIN tbl_Plans AS P ON C.[VGI Plan/MA #]  = P.PlanID
SET P.PlanID = C.[VGI Plan/MA #],
P.PlanName = C.[Plan Name],
P.PlanStatus = C.Status,
P.ClientId = C.[Vision Client Id],
P.ClientName = C.[Company Name],
P.ParentCompany = C.[Parent Company],
P.UserID = UCase([txtUserID]),
P.UpdateTs = Now()
WHERE (((IsNumeric(C.[VGI Plan/MA #]))=True) AND ((C.[Vision Client Id]) Is Not Null));
Chuck LoweAuthor Commented:
Also P.PlanId is a unique key on tbl_Plans.
Rey Obrero (Capricorn1)Commented:
is PlanID an autonumber field?, if it is, remove it from the Update statement.
Chuck LoweAuthor Commented:
@Rey
 PlanID is not an autonumber field.
Rey Obrero (Capricorn1)Commented:
do you have other fields in the table Clients that does not allow duplicates or fields that does not allow null values?
Dale FyeOwner, Developing Solutions LLCCommented:
I agree with Rey that if there are any autonumber fields, or unique indexes in tbl_Plans, you may be violating those and thus not performing the update.  I would also recommend putting the WHERE clause in a subquery against Clients.  Try:

PARAMETERS txtUserID Text ( 255 );
UPDATE (
SELECT Clients.*
FROM Clients
WHERE (IsNumeric(C.[VGI Plan/MA #]))
AND (C.[Vision Client Id] Is Not Null)
) AS C LEFT JOIN tbl_Plans AS P ON C.[VGI Plan/MA #]  = P.PlanID
SET P.PlanID = C.[VGI Plan/MA #],
P.PlanName = C.[Plan Name],
P.PlanStatus = C.Status,
P.ClientId = C.[Vision Client Id],
P.ClientName = C.[Company Name],
P.ParentCompany = C.[Parent Company],
P.UserID = UCase([txtUserID]),
P.UpdateTs = Now()
Chuck LoweAuthor Commented:
@Rey
I'll have to check on the Null issue. All the other key fields (except PlanID) allow duplicates.

@Dale
If after checking for Null issues I'll implement your suggestion.

Thank you both
Chuck LoweAuthor Commented:
@Rey
All fields (except PlanID-Primary Key) are Required=No, Allow zero Length = Yes. The 2 indexed fields are Required = No, Allow zero Length = Yes and Indexed Yes (Duplicates OK) I even removed all the keys (except PlanID) and still had the following issues.

@Dale
 when I run your query I get prompted for txtUserID (which I should) but I also get prompted for C.VGI Pan/MA # and C.Vision Client Id. If I enter nothing into the 2 additional prompts I get the message "You are about to update 0 row(s)" , I click Yes, no rows are aded and none of the existing 22 rows are updated.

If I enter any value in the 2 additional prompts (I entered a 1 for each prompt), I get a message "You are about to update 4901 row(s). I click yes, I get the error "Microsoft Access didn't update 0 field(s) due to a type conversion, 4900 record(s)  due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations. I click Yes. None of the existing 22 rows are updated but one row is added with a PlanID value of 1 and a ClientID value of 1.  As stated above even with all keys removed (except PlanID) the results are the same.
Chuck LoweAuthor Commented:
@Rey & @Dale
I found the issue. I copied the tbl_Plans (structure and data. I ran the original "UPSERT" and it worked fine. I tried it many times, deleting rows from tbl_Plans and manually deleted rows, changed columns to make sure everything updated or added records. I then went thru the Database Documenter and picked the original table, tbl_Plans. It has two relationships on it. One for ClientID (from tbl_Clients to tbl_Plans by ClientId) and is One-ToMany which is not enforced and one for PlanID (from tbl_Plans to tbl_AuditSummary by PlanID) and is One-To_Many which is Enforced.

I verified the issue by trying to delete all the rows in tbl_Plans and 22 did not delete because they are in relationship to tbl_AuditSummary.

I'm assuming the statemnt it doesn't like is P.PlanID = C.[VGI Plan/MA #]. Even though the values stay the same Access must think I'm trying to change the column value.
Is there an easy way to get around this or do I have to go back to the idea of 2 seperate queries. Or maybe 3 queries. One for new rows, one for changed data with no relationship and one for changed data with relationships.

Ideas?
Chuck LoweAuthor Commented:
Do anybody have any other ideas aside from and add query and an update query?
Dale FyeOwner, Developing Solutions LLCCommented:
without actually seeing the query you are running, I cannot make any more recommendations about the "UPSERT" query, but keep in mind, that it will generally only work properly if the tables have a one-to-one or one-to-none relationship.  If there is more than one record in the receiving table that is related to one of the records in the source table, then the query will probably not run, and will return an error.

If this is too difficult, simply write an update query, followed by an append query.

"One for new rows, one for changed data with no relationship and one for changed data with relationships. "

How would you identify "one for changed data with no relationship"?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chuck LoweAuthor Commented:
MERGE was my first choice. Seperate queries for UPDATE and INSERT where my second so I actually accept my own solution.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.