VBA SQL - 将代码从表'Insert Into'更改为'Update'表

Question

我已经寻找答案，无法将答案转换为我的特定代码。我有一些访问数据库的代码，可以用作插入内容，但我希望它更新表。我无法在更改更新后运行它。

以下代码及其所做的工作是添加符合标准到现有表开头的值。但是我希望它在一个名为" Sonoco2016_xlsx"的表中更新现有的空白列" O_stateregion"。我将插入插入到更新的努力失败了。（有关我的努力，请参见第二个代码的示例）

Private Sub InsertStateRegion()
On Error GoTo InsertRegions_Err
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Set db = CurrentDb
Set rs = db.OpenRecordset("Select [OriginState] from [Sonoco2016_xlsx];")
rs.MoveFirst
While Not rs.EOF
    strSQL = "UPDATE [Sonoco2016_xlsx] ([O_StateRegion])"
    strSQL = strSQL & " SELECT [StateRegion] FROM [tblStates]"
    strSQL = strSQL & " WHERE [tblStates].[StateAbbrev]='" & rs![OriginState] & "' "
    db.Execute (strSQL), dbFailOnError
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
Set db = Nothing
btnInsertRegions_Exit:
    Exit Sub
InsertRegions_Err:
    MsgBox Err.Description & " in btnInsertRegions"
    Resume btnInsertRegions_Exit
End Sub

以下是我将其转换为更新

的努力

Private Sub btnInsertRegions_Click()
On Error GoTo InsertRegions_Err
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Set db = CurrentDb
Set rs = db.OpenRecordset("Select [OriginState] from [Sonoco2016_xlsx];")
rs.MoveFirst
While Not rs.EOF
    strSQL = "UPDATE [Sonoco2016_xlsx] ([O_StateRegion])"
    strSQL = strSQL & " SET [Sonoco2016_xlsx].[O_StateRegion]=[tblStates].[StateRegion]"
    strSQL = strSQL & " WHERE [tblStates].[StateAbbrev] = '" & rs![OriginState] & "' "
    db.Execute (strSQL), dbFailOnError
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
Set db = Nothing
btnInsertRegions_Exit:
    Exit Sub
InsertRegions_Err:
    MsgBox Err.Description & " in btnInsertRegions"
    Resume btnInsertRegions_Exit
End Sub

Answer 1

您想要实现的正确语法是

UPDATE [Sonoco2016_xlsx] 
    INNER JOIN [tblStates]
        ON [tblStates].[StateAbbrev] = [Sonoco2016_xlsx].[OriginState]
SET [Sonoco2016_xlsx].[O_StateRegion]=[tblStates].[StateRegion];

您将在不使用RecordSet的情况下执行。

但是，请注意，只有[StateAbbrev]具有唯一的索引，例如如果是[tblStates]的主要键。否则，更新将是模棱两可的。

此外，不可能在

之类的set语句中使用子查询

SET [Sonoco2016_xlsx].[O_StateRegion]=(SELECT [StateRegion] 
                                       FROM = [tblStates] 
                                       WHERE [StateAbbrev] = rs![OriginState])

因为在UPDATE语句中禁止子征服。

Answer 2

这是对我有用的答案，感谢M Doerner！

Private Sub btnInsertRegions_Click()
On Error GoTo InsertRegions_Err
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Set db = CurrentDb
Set rs = db.OpenRecordset("Select [OriginState] from [Sonoco2016_xlsx];")
rs.MoveFirst
While Not rs.EOF
    strSQL = "UPDATE [Sonoco2016_xlsx] INNER JOIN [tblStates]"
    strSQL = strSQL & " ON [tblStates].[StateAbbrev] = [Sonoco2016_xlsx].[OriginState]"
    strSQL = strSQL & " SET [Sonoco2016_xlsx].[O_StateRegion]=[tblStates].[StateRegion]"

    db.Execute (strSQL), dbFailOnError
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
Set db = Nothing
btnInsertRegions_Exit:
    Exit Sub
InsertRegions_Err:
    MsgBox Err.Description & " in btnInsertRegions"
    Resume btnInsertRegions_Exit
End Sub