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

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.

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.

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

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.*;

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.

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'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

@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

@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));

Also P.PlanId is a unique key on tbl_Plans.

is PlanID an autonumber field?, if it is, remove it from the Update statement.

@Rey
 PlanID is not an autonumber field.

do you have other fields in the table Clients that does not allow duplicates or fields that does not allow null values?

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()

@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

@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.

Do anybody have any other ideas aside from and add query and an update query?

MERGE was my first choice. Seperate queries for UPDATE and INSERT where my second so I actually accept my own solution.