SQL SQL访问查询-如果存在则更新行，如果不存在则插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5297383/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL access query- Update row if exists, insert if does not
提问by Urbycoz
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.
我需要为 MS Access 2000 编写一个 SQL 查询，以便更新一行（如果存在），但如果不存在则插入。
i.e.
IE
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 methodthat works in MySQL doesn't seem to work here.)
（适用于 MySQL的 ON DUPLICATE KEY UPDATE 方法在这里似乎不起作用。）
回答by ypercube??
Not in one query but you could do two queries for multiple rows.
不是在一个查询中，但您可以对多行进行两次查询。
In MySQL, the equivalent is (as you already know :)
在 MySQL 中，等价物是（如您所知：）
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 INand NOT INsubqueries could be possibly converted to equivalent JOINand LEFT JOIN with check for NOT NULLforms.
*请注意，IN和NOT IN子查询可能会转换为等效的JOIN和LEFT JOIN with check for NOT NULL形式。
回答by David Mason
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):
这并不直接适用于 Access [编辑：David-W-Fenton 断言这在 access 中是不可能的]，但为了完整性（以防有人阅读此内容对 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:
我使用一种应该更有效的方法在 Microsoft SQL Server 中取得了成功，因为它只需要进行一次索引检查，而不是两次。这是我当前项目中的一个示例：
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)
