In the case of Postgre SQL, it can be done using a FROM clause with UPDATE, like this: This does an INNER JOIN between the tables “staff” and “updates” where the column “name” matches.
The SET clause then takes the “salary” field from the “updates” table and uses it to update the “salary” field of the “staff” table.
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.
(It will use placeholders and parameter binding if it thinks it’s appropriate.) If given our second example with two distinct values, will spot that there are two distinct values, 12, and will effect this with two UPDATE statements as described above.
In the case where the application server and database server are on different hosts, the round-trip will involve network latency as well.
But if there are a large number of rows that require an update, then the overhead of issuing large numbers of UPDATE statements can result in the operation as a whole taking a long time to complete.
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.
OK, that’s great, we have some theoretical approaches for reducing the number of queries, now what?
The code required to implement the above logic is sufficiently fiddly that we would probably not want to have to repeat it.