在MS-access中更新

Question

我需要为MS-Access 2000编写一个SQL查询，以便行在存在时被更新，在不存在时被插入。(我相信这被称为"upsert")

即

如果行存在...

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

如果它不存在...

INSERT INTO Table1 VALUES (...)

这可以在一个查询中完成吗？

Answer 1

您可以通过使用带有LEFT JOIN的UPDATE查询来模拟Access中的upsert。

update b
left join a on b.id=a.id
set a.f1=b.f1
, a.f2=b.f2
, a.f3=b.f3

Answer 2

假设Column1上有一个惟一的索引，您可以使用DCount表达式来确定是否有零行或一行的Column1 = 'SomeValue‘。然后基于该计数进行INSERT或UPDATE。

If DCount("*", "Table1", "Column1 = 'SomeValue'") = 0 Then
    Debug.Print "do INSERT"
Else
    Debug.Print "do UPDATE"
End If

我更喜欢这种方法，而不是首先尝试INSERT，捕获3022键冲突错误，然后执行UPDATE以响应错误。然而，我不能从我的方法中获得巨大的好处。如果您的表包含自动编号字段，避免失败的INSERT将阻止您不必要地使用下一个自动编号值。我还可以避免在不需要的时候构建INSERT字符串。Access Cookbook告诉我，字符串连接在VBA中是一个开销中等的操作，所以我会寻找机会避免构建字符串，除非确实需要它们。这种方法还可以避免为不需要的INSERT创建锁。

然而，这些原因对你来说可能都不是很有说服力。坦率地说，我认为我在这种情况下的偏好可能是关于我“感觉正确”的东西。我同意@David-W-Fenton对previous Stack Overflow question的评论：“最好编写SQL，这样就不会试图追加已经存在的值--也就是说，从一开始就防止错误发生，而不是依赖数据库引擎来挽救您自己。”

Answer 3

如果表有唯一的键，"upsert“是可能的。

这篇来自Smart Access的老技巧是我最喜欢的：

使用一个查询更新和追加记录的

作者: Alan Biggs

您是否知道可以在Access中使用更新查询同时更新和添加记录？如果您有两个版本的表，即tblOld和tblNew，并且希望将tblNew中的更改集成到tblOld中，这将非常有用。

请按照以下步骤操作：

创建一个update查询并添加两个表。通过将tblNew的键字段拖到tblOld的匹配字段上，将两个表连接起来。

1. 双击该关系并选择join选项，该选项包括来自tblNew的所有记录，并且仅包括来自tblOld的匹配记录。
2. 从tblOld中选择所有字段，并将它们拖到QBE网格上。对于每个字段，在更新为单元格类型中的
3. 中键入tblNew.FieldName，其中FieldName与视图菜单中的tblOld.
4. Select查询属性的字段名称相匹配，并将唯一记录更改为False。(这将关闭SQL视图中的DISTINCTROW选项。如果您保留此选项，您将在结果中只得到一条空白记录，但是您希望为每个新记录添加一个空白记录到tblOld中。)
5. 运行查询，您将看到对tblNew的更改现在位于tblOld中。

这只会将已添加到tblNew的记录添加到tblOld。tblOld中不存在于tblNew中的记录仍将保留在tblOld中。