我的SQL陈述式错误(UPSERT) [英] Error in my SQL statement (UPSERT)

问题描述

我正在尝试执行UPSERT(因为这似乎是它的名字)，但出现错误:Microsoft JET Database Engine error '80040e14' Syntax error (missing operator) in query expression ...

I'm trying to do an UPSERT (since that's what it appears to be called) and I'm getting an error: Microsoft JET Database Engine error '80040e14' Syntax error (missing operator) in query expression ...

UPDATE myTable
SET Field1='10', Field2='11'
WHERE Date = #06/05/2013#
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO myTable
(Field1, Field2)
VALUES (10, 11)
END

推荐答案

您的代码正在使用Access SQL无法理解的T-SQL(SQL Server)语法. VBA等效为:

Your code is using T-SQL (SQL Server) syntax that Access SQL does not understand. The VBA equivalent would be:

Dim cdb As DAO.Database
Set cdb = CurrentDb
cdb.Execute "UPDATE myTable SET Field1='10', Field2='11' WHERE [Date] = #2013-06-05#", dbFailOnError
If cdb.RecordsAffected = 0 Then
    cdb.Execute "INSERT INTO myTable (Field1, Field2) VALUES ('10', '11')", dbFailOnError
End If
Set cdb = Nothing

注意:

1. Date是Access中的保留字，因此您应将字段名称指定为[Date].

1. Date is a reserved word in Access, so you should specify the field name as [Date].

请注意，代码使用明确的日期格式yyyy-mm-dd.每当您将日期文字包含在井号(#)中时，都应使用该格式.

Notice that the code uses the unambiguous date format yyyy-mm-dd. You should use that format any time you have a date literal enclosed in hash marks (#).

还请注意，它可以修复代码中的类型不匹配:UPDATE语句尝试将字段更新为文本，而INSERT语句尝试将其作为数字插入.

Note also that it fixes the type mismatch in your code: The UPDATE statement tried to update the fields as text, while the INSERT statement tried to insert them as numbers.

编辑以下内容:评论

上面的ADO等同于

Edit re: comment

The ADO equivalent of the above would be

conntemp.Execute "UPDATE myTable SET Field1='10', Field2='11' WHERE [Date] = #2013-06-05#", RecordsAffected
If RecordsAffected = 0 Then
    conntemp.Execute "INSERT INTO myTable (Field1, Field2) VALUES ('10', '11')"
End If

这篇关于我的SQL陈述式错误(UPSERT)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！