MS Access: how to achieve ON DUPLICATE KEY UPDATE?

Question

Trying to avoid separate queries to check for key existance and then inserting or updating accordingly. From Googling around it seems that Access doesn't support SQL server's MERGE either.

Thanks

Answer 1

Actually, Access does support "Upserting" as it is sometimes called. I found this solution in a book called Microsoft Access Solutions - Tips, Tricks and Secrets from Microsoft Access MVPs written by Arvin Meyer and Douglas J. Steele.

Here's a query that takes the new data from the Contacts1 table and applies it to the Contacts2 table:

UPDATE Contacts2 LEFT JOIN Contacts1
ON Contacts2.ContactID = Contacts1.ContactID
SET Contacts2.ContactID = [Contacts1].[ContactID],
Contacts2.FirstName = [Contacts1].[FirstName],
Contacts2.LastName = [Contacts1].[LastName],
Contacts2.MobilePhone = [Contacts1].[MobilePhone]

Naturally, I think you could use table aliases and where conditions. And I'm not sure the brackets are needed as shown but I put them in there since the example in the book shows them (even though the table names and field names contain no spaces or symbols).

And here's another note taken directly from the book:

It should be obvious that this only works if you have a unique index defined for each table. Usually this would be the primary key of the table, but it's not actually necessary that it be as long as the candidate key exists in both tables. (This allows you to use an AutoNumber as the primary key and not have to worry that the numbers are different.) It doesn't matter whether the index contains a single field or is a compound key index, as long as it's the same in both tables.

Answer 2

A simple update query can do the trick.
Create a simple Select query.
Then in the design view of your query builder click on Update.
Take a look at the changes in the grid.
Now put the expression(s) or value(s) with which you are going to update your field(s).
Run the query