在MS-access中升级 [英] Upserting in MS-access

问题描述

我需要为MS-Access 2000编写一个SQL查询，以便在存在行的情况下进行更新，但在不存在的情况下进行插入. (我相信这被称为"upsert")

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 believe this is called an "upsert")

即

如果行存在...

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

如果不存在...

INSERT INTO Table1 VALUES (...)

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

Can this be done in one query?

推荐答案

我通常首先运行insert语句，然后检查是否发生错误3022，这表明该行已经存在.像这样:

I usually run the insert statement first and then I check to see if error 3022 occurred, which indicates the row already exists. So something like this:

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

我想提一下，我在这里发布的内容是一个非常常见的解决方案，但是您应该意识到，对错误进行计划并将其用作程序正常流程的一部分通常被认为是一个坏主意，尤其是在有其他方法的情况下获得相同的结果.感谢RolandTumble指出了这一点.

I want to mention that what I've posted here is a very common solution but you should be aware that planning on errors and using them as part of the normal flow of your program is generally considered a bad idea, especially if there are other ways of achieving the same results. Thanks to RolandTumble for pointing this out.

这篇关于在MS-access中升级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！