SQL访问查询-更新行(如果存在)，如果不存在则插入 [英] SQL access query- Update row if exists, insert if does not

问题描述

我需要为MS Access 2000编写一个SQL查询，以便在存在行的情况下进行更新，但在不存在的行中进行插入.

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.

即

如果行存在...

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

如果不存在...

INSERT INTO Table1 VALUES (...) 

可以在一个查询中完成吗?

Can this be done in one query?

(在MySQL中有效的重复密钥更新方法似乎在这里不起作用.)

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

推荐答案

不在一个查询中，但是您可以对多个行执行两个查询.

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

在MySQL中，等效项是(您已经知道:)

In MySQL, the equivalent is (as you already know :)

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

或

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.

*请注意，IN和NOT IN子查询可能会转换为等效的JOIN和LEFT JOIN with check for NOT NULL形式.

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

这篇关于SQL访问查询-更新行(如果存在)，如果不存在则插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！