在单个 SQL Server 查询中更新多行 - 在 VBA 中 [英] Update multiple rows in a single SQL Server query - in VBA
本文介绍了在单个 SQL Server 查询中更新多行 - 在 VBA 中的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在使用 VBA 通过以下代码更新 Access 数据库:
I am using VBA to update an Access database with the following code:
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).
That works but can be very slow since there are between 5,000 and 10,000 rows to update (imax).
有没有办法改用单个 SQL 语句?
Is there a way to use a single SQL statement instead?
我在下面尝试了 Jatin 的建议.不幸的是,它并没有在我身边飞行(尝试下面的前三行):
I've tried Jatin's suggestion below. Unfortunately it doesn't fly on my side (a try on the first three rows below):
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 专家,但像下面这样的东西可以完成任务.
I am not expert to VBA, but something like below will do the task.
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 构建值表.
below is an alternate solution, it builds value table with 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)
这篇关于在单个 SQL Server 查询中更新多行 - 在 VBA 中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文