Phil Phil - 5 months ago 28
SQL Question

Update multiple rows in a single SQL Server query - in VBA

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


That works but can be very slow since there are between 5,000 and 10,000 rows to update (imax).

Is there a way to use a single SQL statement instead?

Answer

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)

If above method do not work for Access try this, 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)