SQL Access Request - Refresh a row if exists, insert if not

Question

I need to write an SQL query for MS Access 2000 so that the row is updated if it exists, but inserted if it does not work.

i.e.

If the string exists ...

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

If it does not exist ...

 INSERT INTO Table1 VALUES (...) 

Can this be done in a single request?

(The ON DUPLICATE KEY UPDATE method that works in MySQL does not seem to work here.)

Answer 1

Not in one query, but you can make two queries for multiple rows.

The MySQL equivalent (as you already know :)

 INSERT INTO Table1 (...) VALUES(...) ON DUPLICATE KEY UPDATE column=column+1 ; 

or

 INSERT INTO Table1 (...) ( SELECT ... FROM ... ) ON DUPLICATE KEY UPDATE column=column+1 ; 

The second form can be written with two queries as:

 UPDATE Table1 SET (...) WHERE Column1 = 'SomeValue' ; INSERT INTO Table1 (...) ( SELECT ... FROM ... WHERE 'SomeValue' NOT IN ( SELECT Column1 FROM Table1 ) ) ; 

You can also reorder and insert new rows first and then update all rows if this suits your data better.

* Note that the IN and NOT IN subqueries may possibly be converted to the equivalent JOIN and LEFT JOIN with check for NOT NULL forms LEFT JOIN with check for NOT NULL .

Answer 2

This does not apply directly to Access [EDIT: David-W-Fenton claims it is not possible to access] , but for completeness (in case someone reading this is interested in something outside of Access):

I have had success in Microsoft SQL Server using an approach that should be more efficient, since it should only perform one index check, not two. Here is an example from my current project:

 UPDATE ActivityRelationships SET [Count] = ([Count] + 1) WHERE ActivityBeforeId=@activityBeforeId AND ActivityAfterId=@activityAfterId IF @@ROWCOUNT=0 INSERT INTO ActivityRelationships ([ActivityBeforeId], [ActivityAfterId], [Count]) VALUES (@activityBeforeId, @activityAfterId, 1)