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