Upserting in MS-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?

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

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