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