Mysql updating multiple tables
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.Also, let’s say we want to make updates only for people in the company “Widget Co”.“Bob Jones” appears in both companies, so we need to be careful to identify the company, but we don’t want to have to specify it repeatedly for each row.So we could think in terms of creating a re-usable module which would implement that logic.
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.
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.
(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.
Optimising the number of UPDATEs by grouping the distinct SET values can be done in a way which is compatible with most common SQL databases. FROM approach requires knowledge of the specific SQL database being used.