在 Microsoft Access 中更新插入

Question

我需要为 MS-Access 2000 编写一个 SQL 查询，以便更新行（如果存在），但如果不存在则插入。 （我相信这称为“upsert”）

即

如果行存在...

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

如果它不存在...

INSERT INTO Table1 VALUES (...)

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

Answer 1

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

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

（编辑，2023 年 8 月）

有些人可能会发现这种形式更容易理解：

UPDATE main_table RIGHT JOIN new_data 
    ON main_table.id = new_data.id
SET
    main_table.id = new_data.id,
    main_table.col_1 = new_data.col_1,
    main_table.col_2 = new_data.col_2

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 对 上一个堆栈溢出问题：“最好编写 SQL，这样您就不会尝试附加已经存在的值——即，从一开始就防止错误发生，而不是依赖数据库引擎来保存你从你自己开始。”

Answer 3

如果表具有唯一键，则可以进行“更新插入”。

Smart Access 的这个旧技巧是我最喜欢的技巧之一：

通过一次查询更新和追加记录

作者：艾伦·比格斯

您是否知道可以在 Access 中使用更新查询来更新
并同时添加记录？如果您有两个，这很有用
表 tblOld 和 tblNew 的版本，并且您想要集成
从 tblNew 更改为 tblOld。

请按照以下步骤操作：

创建更新查询并添加两个表。通过以下方式连接两个表
将 tblNew 的关键字段拖到 tblOld 的匹配字段上。

1. 双击关系并选择连接选项，其中包括 tblNew 中的所有记录以及仅与 tblNew 匹配的记录
   tbl老。

2. 选择 tblOld 中的所有字段并将它们拖到 QBE 网格上。

3. 对于每个字段，在“更新到”单元格中键入 tblNew.FieldName，其中 FieldName 与 tblOld 的字段名称匹配。

4. 从“视图”菜单中选择“查询属性”并将“唯一记录”更改为“假”。 （这会关闭 SQL 中的 DISTINCTROW 选项
   看法。如果您保留此选项，您将在您的文件夹中仅得到一条空白记录
   结果，但您希望为每条新记录添加一条空白记录
   到tblOld。）

5. 运行查询，您将看到 tblNew 的更改现在位于 tblOld 中。

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

Answer 4

我通常先运行插入语句，然后检查是否发生错误 3022，这表明该行已经存在。所以像这样：

On Error Resume Next
CurrentDb.Execute "INSERT INTO Table1 (Fields) VALUES (Data)", dbFailOnError
If Err.Number = 3022 Then
    Err.Clear        
    CurrentDb.Execute "UPDATE Table1 SET (Fields = Values) WHERE Column1 = 'SomeValue'", dbFailOnError
ElseIf Err.Number <> 0 Then
    'Handle the error here
    Err.Clear
End If

编辑1：
我想提一下，我在这里发布的内容是一个非常常见的解决方案，但您应该意识到，计划错误并将其用作程序正常流程的一部分通常被认为是一个坏主意，特别是如果有其他方法达到相同的结果。感谢 RolandTumble 指出了这一点。

Answer 5

您不需要捕获错误。相反，只需运行 INSERT 语句，然后检查

CurrentDb.RecordsAffected

它是 1 还是 0，具体取决于情况。

注意：针对 CurrentDB 执行并不是一个好习惯。最好将数据库捕获到局部变量：

Dim db As DAO.Database
Set db = CurrentDb
db.Execute(INSERT...)
If db.RecordsAffected = 0 Then
  db.Execute(UPDATE...)
End If

Answer 6

正如其他人提到的，您可以使用新表作为左侧来使用 UPDATE LEFT JOIN 进行 UPSERT 。这将添加所有丢失的记录并更新匹配的记录，使已删除的记录保持不变。

如果我们遵循 创建并运行更新查询文章 我们最终会得到如下所示的 SQL：

UPDATE Table1 
INNER JOIN NewTable1 ON Table1.ID = NewTable1.ID 
SET Table1.FirstName = [NewTable1].[FirstName] 

但是内部联接只会更新匹配的记录，不会添加新记录。因此，让我们将 INNER 更改为 LEFT：

UPDATE Table1 
LEFT JOIN NewTable1 ON Table1.ID = NewTable1.ID 
SET Table1.FirstName = [NewTable1].[FirstName]

现在保存数据库的副本。在主数据库上运行此测试之前，请先在副本上运行测试。