我正在使用VBA通过以下代码更新Access数据库:
For i = 1 To imax
strSql = "UPDATE Products SET Price ='" & vArray(i, 2) & "' WHERE ID = '" & vArray(i, 1) & "';"
connDB.Execute (strSql)
Next i
这行得通,但可能非常慢,因为有5,000至10,000行需要更新(imax)。
有没有一种方法可以使用单个SQL语句?
我在下面尝试了Jatin的建议。不幸的是,它并没有在我这边飞(尝试下面的前三行):
UPDATE p SET p.Price = x.SetValue FROM Products p INNER JOIN (
SELECT '149' AS SetValue, 'P100005' AS WhereValue UNION ALL
SELECT '129' AS SetValue, 'P100001' AS WhereValue UNION ALL
SELECT '99' AS SetValue, 'P100002' AS WhereValue ) AS x ON p.ID = x.WhereValue;
我不是VBA专家,但是下面的任务可以完成任务。
strValues = "INNER JOIN (VALUES "
For i = 1 To imax
strValues = strValues & IIF(i = 1,"",",") & "('" & vArray(i, 2) & "', '" & vArray(i, 1) & "')"
Next i
strValues = strValues & " ) AS x(SetValue, WhereValue) ON p.ID = x.WhereValue "
strSql = "UPDATE p SET p.Price = x.SetValue FROM Products p " & strValues
connDB.Execute (strSql)
下面是一个替代解决方案,它使用UNION ALL建立值表。
strValues = "INNER JOIN ("
For i = 1 To imax
strValues = strValues & vbCrLf & "SELECT '" & vArray(i, 2) & "' AS SetValue, '" & vArray(i, 1) & "' AS WhereValue " & IIF(i = imax,""," UNION ALL ") 'skip Union All for last entry
Next i
strValues = strValues & " ) AS x ON p.ID = x.WhereValue "
strSql = "UPDATE p SET p.Price = x.SetValue FROM Products p " & strValues
connDB.Execute (strSql)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句