vba 如果存在，更新，否则插入新记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2992242/
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
if exists, update, else insert new record
提问by l--''''''---------''''''''''''
i am inserting values into a table
我正在将值插入表中
if the record exists already replace it, and if it does not exist then add a new one.
如果记录存在则替换它，如果它不存在则添加一个新记录。
so far i have this code:
到目前为止，我有这个代码：
INSERT INTO table_name
VALUES (value1, value2, value3,...) where pk="some_id";
but i need something like this
但我需要这样的东西
if not pk="some_id" exists then INSERT INTO table_name
VALUES (value1, value2, value3,...) where pk="some_id"; else update table_name where pk="some_id"
what would be the correct SQL syntax for this?
什么是正确的 SQL 语法？
please note that i am using sql access and that i guess it can be a combination of vba and sql
请注意，我正在使用 sql 访问，我猜它可以是 vba 和 sql 的组合
回答by HansUp
First update rows which match between your import table and master table.
首先更新导入表和主表之间匹配的行。
UPDATE table_name AS m
INNER JOIN tblImport AS i
ON m.pk = i.pk
SET
m.field2 = i.field2,
m.field3 = i.field3,
m.field4 = i.field4;
Then add any imported records which don't exist in the master table.
然后添加主表中不存在的所有导入记录。
INSERT INTO table_name (
pk,
field2,
field3,
field4)
SELECT
i.pk,
i.field2,
i.field3,
i.field4
FROM
tblImport AS i
LEFT JOIN table_name AS m
ON i.pk = m.pk
WHERE
(((m.pk) Is Null));
回答by Philippe Grondier
This could be done easily with recordsets. The code would then look like that (for an ADODB recordset):
这可以通过记录集轻松完成。然后代码看起来像这样（对于 ADODB 记录集）：
myRecordset.find ....
if myRecordset.EOF then
myRecordset.addNew
endif
....
myRecordset.fields(...) = ...
....
myRecordset.update
回答by David-W-Fenton
I have posted about my approach to this problem many, many times in many different forums, but I'll just recapitulate the basic structure of the approach I use. There is no way to do it in one step, though.
我已经在许多不同的论坛上多次发布了关于我解决这个问题的方法，但我只会概括一下我使用的方法的基本结构。但是，没有办法一步完成。
update the existing records from the external data source.
insert records that don't already exist.
从外部数据源更新现有记录。
插入不存在的记录。
This assumes a common primary key that can be used to link the existing table with the external data source.
这假定一个公共主键可用于将现有表与外部数据源链接。
Task #2 is pretty trivial, just an outer join for the records that don't already exist.
任务#2 非常简单，只是对尚不存在的记录进行外连接。
One can use brute force for #1, writing an UPDATE statement with a SET for each field other than the primary key, but I consider that to be messy and unnecessary. Also, since I have a lot of replicated applications, I can't do that, as it would result in false conflicts (when a field is updated to the same value as it started with).
可以对 #1 使用蛮力，为除主键之外的每个字段编写一个带有 SET 的 UPDATE 语句，但我认为这是混乱和不必要的。另外，由于我有很多复制的应用程序，我不能这样做，因为它会导致错误的冲突（当一个字段更新为与它开始时相同的值时）。
So, for that purpose, I use DAO and write an on-the-fly SQL statement to update COLUMN-BY-COLUMN. The basic structure is something like this:
因此，为此，我使用 DAO 并编写了一个动态 SQL 语句来更新 COLUMN-BY-COLUMN。基本结构是这样的：
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim strField As String
Dim strSet As String
Dim strWhere As String
Dim strSQL As String
Set db = CurrentDB
Set rs = db.OpenRecordset("DestinationTable")
For Each fld in rs.Fields
strField = fld.Name
If strField <> "PKField" Then
strSet = "DestinationTable." & strField & " = ExternalTable." & strField
strWhere = "Nz(DestinationTable." & strField & ",'') = Nz(ExternalTable." & strField & ", '')"
strSQL = "UPDATE DestinationTable "
strSQL = strSQL & " SET " & strSet
strSQL = strSQL & " WHERE " & strWhere
db.Execute strSQL, dbFailOnError
Debug.Print strField & ": " & db.RecordsAffected
End If
Next fld
Now, the complicated part is handling numeric vs. date vs. string fields, so you have to have some logic to write the WHERE clauses to use proper quotes and other delimiters according to the field type. Rather than test the field type, I generally just use a CASE SELECT like this, making string fields the default:
现在，复杂的部分是处理数字、日期和字符串字段，因此您必须有一些逻辑来编写 WHERE 子句，以根据字段类型使用正确的引号和其他分隔符。我通常不测试字段类型，而是使用这样的 CASE SELECT，将字符串字段设为默认值：
Dim strValueIfNull As String
Select Case strField
Case "DateField1", "DateField2", "NumericField2", "NumericField2", "NumericField3"
strValueIfNull = "0"
Case Else
strValueIfNull = "''"
strWhere = "Nz(DestinationTable." & strField & ", '') = Nz(ExternalTable." & strField & ", '')"
End Select
strWhere = "Nz(DestinationTable." & strField & ", " & strValueIfNull & ") = Nz(ExternalTable." & strField & ", " & strValueIfNull & ")"
I could have the details there wrong, but you get the idea, I think.
我可能把那里的细节弄错了，但你明白了，我想。
This means you'll run only as many SQL updates as there are updatable fields, and that you'll only update records that need updating. If you're also stamping your records with a "last updated" date, you'd do that in the UPDATE SQL and you'd only want to do that on the records that really had different values.
这意味着您将只运行与可更新字段一样多的 SQL 更新，并且您将只更新需要更新的记录。如果您还用“上次更新”日期标记您的记录，您将在 UPDATE SQL 中执行此操作，并且您只想对真正具有不同值的记录执行此操作。
