Upserting in Microsoft Access

Question

I need to write an SQL query for MS-Access 2000 so that a row is updated if it exists, but inserted if it does not. (I believe this is called an "upsert")

i.e.

If row exists...

UPDATE Table1 SET (...) WHERE Column1='SomeValue'

If it does not exist...

INSERT INTO Table1 VALUES (...)

Can this be done in one query?

Tidal

See this answer. Summary: can't be done in one SQL statement. Consider if you can use a DELETE followed by an INSERT (won't work if those rows are referenced by other tables). – Homeopathist

Also, if it's replicated MDB, deletions will be quite problematic. Likewise, if there are many records being deleted, you'll be fragmenting your data file, no matter whether it's MDB or ACCDB format. Basically, I think the DELETE approach is a very bad idea. – Sartin

Answer 1

You can simulate an upsert in an Access by using an UPDATE query with a LEFT JOIN.

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

(Edit, August 2023)

Some people might find this form easier to understand:

UPDATE main_table RIGHT JOIN new_data 
    ON main_table.id = new_data.id
SET
    main_table.id = new_data.id,
    main_table.col_1 = new_data.col_1,
    main_table.col_2 = new_data.col_2

Woodsy

To make it more clear, this query updates a with values from b. (For the newbs out there!) – Crude

Sorry I am very new to this. Can someone explains why this would work? Isn't this answer updating b but actually updating values in a? And how does this answer insert new records? I only understand the "update" part here but don't understand the "insert" part. Correct me if I am wrong. – Halcomb

@EmoryLu see PipperChip remark: the code updates a. The trick is that with that construction, if there is no matching row in a, the row will be created. That is the principle of an UPSERT (UPdate or inSERT) – Woodsy

@Woodsy Thank you. Your approach is brilliant! – Halcomb

Answer 2

Assuming a unique index on Column1, you can use a DCount expression to determine whether you have zero or one row with Column1 = 'SomeValue'. Then INSERT or UPDATE based on that count.

If DCount("*", "Table1", "Column1 = 'SomeValue'") = 0 Then
    Debug.Print "do INSERT"
Else
    Debug.Print "do UPDATE"
End If

I prefer this approach to first attempting an INSERT, trapping the 3022 key violation error, and doing an UPDATE in response to the error. However I can't claim huge benefits from my approach. If your table includes an autonumber field, avoiding a failed INSERT would stop you from expending the next autonumber value needlessly. I can also avoid building an INSERT string when it's not needed. The Access Cookbook told me string concatenation is a moderately expensive operation in VBA, so I look for opportunities to avoid building strings unless they're actually needed. This approach will also avoid creating a lock for an unneeded INSERT.

However, none of those reasons may be very compelling for you. And in all honesty I think my preference in this case may be about what "feels right" to me. I agree with this comment by @David-W-Fenton to a previous Stack Overflow question: "It's better to write your SQL so you don't attempt to append values that already exist -- i.e., prevent the error from happening in the first place rather than depending on the database engine to save you from yourself."

Epochal

Thanks for the quote! I am in full agreement with myself! :) Anyway, I'd do this with a LEFT JOIN, and insert only the records that don't already exist. – Sartin

Answer 3

An "upsert" is possible, if the tables have a unique key.

This old tip from Smart Access is one of my favourites:

Update and Append Records with One Query

By Alan Biggs

Did you know that you can use an update query in Access to both update and add records at the same time? This is useful if you have two versions of a table, tblOld and tblNew, and you want to integrate the changes from tblNew into tblOld.

Follow these steps:

Create an update query and add the two tables. Join the two tables by dragging the key field of tblNew onto the matching field of tblOld.

1. Double-click on the relationship and choose the join option that includes all records from tblNew and only those that match from tblOld.

2. Select all the fields from tblOld and drag them onto the QBE grid.

3. For each field, in the Update To cell type in tblNew.FieldName, where FieldName matches the field name of tblOld.

4. Select Query Properties from the View menu and change Unique Records to False. (This switches off the DISTINCTROW option in the SQL view. If you leave this on you'll get only one blank record in your results, but you want one blank record for each new record to be added to tblOld.)

5. Run the query and you'll see the changes to tblNew are now in tblOld.

This will only add records to tblOld that have been added to tblNew. Records in tblOld that aren't present in tblNew will still remain in tblOld.

Radical

What if you want to UPSERT a single table that has no relationship with any other table or query? – Bengal

@Fandango68: The above method updates one table only. – Radical

Answer 4

I usually run the insert statement first and then I check to see if error 3022 occurred, which indicates the row already exists. So something like this:

On Error Resume Next
CurrentDb.Execute "INSERT INTO Table1 (Fields) VALUES (Data)", dbFailOnError
If Err.Number = 3022 Then
    Err.Clear        
    CurrentDb.Execute "UPDATE Table1 SET (Fields = Values) WHERE Column1 = 'SomeValue'", dbFailOnError
ElseIf Err.Number <> 0 Then
    'Handle the error here
    Err.Clear
End If

Edit1:
I want to mention that what I've posted here is a very common solution but you should be aware that planning on errors and using them as part of the normal flow of your program is generally considered a bad idea, especially if there are other ways of achieving the same results. Thanks to RolandTumble for pointing this out.

Jumper

This is skirting the edge of a downvote, for me. Using errors in the normal flow of processing is a Bad Idea in my book--not just a code smell, but a reek. HansUp has given a much better answer, IMHO. However, it's sometimes unavoidable, so I won't actually vote.... – Echolalia

Thanks for you comment RolandTumble. You've just confirmed my suspicion that it's a bad practice to use error conditions as normal flow. It's something I've wondered about but I hadn't really reached a conclusion. – Jumper

Why wouldn't you wrap this in a transaction, instead? – Sartin

Answer 5

You don't need to catch the error. Instead, just run the INSERT statement and then check

CurrentDb.RecordsAffected

It will either be 1 or 0, depending.

Note: It's not good practice to execute against CurrentDB. Better to capture the database to a local variable:

Dim db As DAO.Database
Set db = CurrentDb
db.Execute(INSERT...)
If db.RecordsAffected = 0 Then
  db.Execute(UPDATE...)
End If

Dyewood

Answer 6

As others have mentioned, You can UPSERT with an UPDATE LEFT JOIN using the new table as the left hand side. This will add all missing records and update matching records, leaving deleted records intact.

If we follow the Create and run an update query Article we will end up with SQL that looks like this:

UPDATE Table1 
INNER JOIN NewTable1 ON Table1.ID = NewTable1.ID 
SET Table1.FirstName = [NewTable1].[FirstName] 

but an inner join will only update matching records, it won't add new records. So let's change that INNER to a LEFT:

UPDATE Table1 
LEFT JOIN NewTable1 ON Table1.ID = NewTable1.ID 
SET Table1.FirstName = [NewTable1].[FirstName]

Now save a copy of the DB. Run a test on the copy before you run this on your primary DB.

Andrien