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

The simple answer is No. However, you can write a procedure using VBA to do such a thing. — shahkalpesh, Mar 14 '11 at 11:22
what about [Merge Statement][1] ? [1]: http://technet.microsoft.com/en-us/library/bb510625.aspx — mcha, Mar 14 '11 at 12:30
The cited Technet article is for T-SQL, so take a guess if it works in Access. — David-W-Fenton, Mar 15 '11 at 20:35

score 8 · Accepted Answer · answered Mar 14 '11 at 12:55

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.

David Mason · Answer 2 · 2011-06-02T08:23:34.297

-1

This doesn't apply directly to Access [EDIT: David-W-Fenton asserts that this is not possible in access], but for completeness (in case someone reading this is interested in something beyond Access):

I have had success in Microsoft SQL Server using an approach that should be more efficient as it only has to do one index check, rather than two. Here's 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)

edited Jun 02 '11 at 08:23

answered May 26 '11 at 16:44

David Mason

2,917
4
30
45

5

It can't be done in Access. -1 for posting an answer that is useless for the specific question. – David-W-Fenton May 28 '11 at 22:30
I wasn't proposing this as a definite solution to the question, just something that might be worth looking into whether it is possible. – David Mason Jun 02 '11 at 08:20
2

"This doesn't apply directly to Access" on a question about access should have been your hint that you would get downvoted. – Tim Seguine Sep 06 '13 at 10:25

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

2 Answers2

Linked