Updating multiple fields in access
So, given a list of updates to apply we could effect them using the following steps: So in the example above we can reduce five statements to four. But now the number of statements is no longer directly dependent on the number of rows requiring updates.Even if we wanted to update a thousand rows with different values, we could still do it with four statements.So we could think in terms of creating a re-usable module which would implement that logic.This is the intention of UPDATE staff SET salary = 1200 WHERE name = ' Bob'; UPDATE staff SET salary = 1200 WHERE name = ' Jane'; UPDATE staff SET salary = 1200 WHERE name = ' Frank'; UPDATE staff SET salary = 1200 WHERE name = ' Susan'; UPDATE staff SET salary = 1200 WHERE name = ' John'; “key_columns” specifies the columns which will be used to identify rows which need to be updated (using WHERE). The first element provides the value of the column (specified by “key_columns”) to identify the row to be updated.The dominant factor in the time taken to complete the overall operation tends to be the “admin” work in conveying the application’s intention to the database server rather than the actual updates to the database.A more effective solution to this problem is to attempt to reduce the number of UPDATE statements.
The traditional advice for improving performance for multiple UPDATE statements is to “prepare” the required query once, and then “execute” the prepared query once for each row requiring an update.
But in many cases this only provides a modest improvement as each UPDATE operation still requires a round-trip communication with the database server.
In the case where the application server and database server are on different hosts, the round-trip will involve network latency as well.
We can easily contrive for an “updates” table to exist by creating a temporary table and populating it.
It is relatively straightforward to populate a table with multiple rows with just one query (or at least, far fewer queries than the number of rows desired).