SQL access query- Update row if exists, insert if does not

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

(The ON DUPLICATE KEY UPDATE method that works in MySQL doesn't seem to work here.)

Answer 1

Not in one query but you could do two queries for multiple rows.

In MySQL, the equivalent is (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 could also reverse the order and first insert the new rows and then update all rows if that fits with your data better.

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