Sql updating multiple tables at once

Let us start with a simple table: 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'; UPDATE staff SET salary = 1200 WHERE name = ' Bob'; UPDATE staff SET salary = 1250 WHERE name = ' Jane'; UPDATE staff SET salary = 1200 WHERE name = ' Frank'; UPDATE staff SET salary = 1250 WHERE name = ' Susan'; UPDATE staff SET salary = 1200 WHERE name = ' John'; We are no longer setting all the salary fields to the same value, so we can’t collapse it into a single statement.But we can group the updates according to the value being set, and then do one UPDATE statement per distinct value.We can easily contrive for an “updates” table to exist by creating a temporary table and populating it.

A requirement arises in many systems to update multiple SQL database rows.

For small numbers of rows requiring updates, it can be adequate to use an UPDATE statement for each row that requires an update.

So if the caller has a Postgre SQL database, and calls with data to represent our third example (where the target values are all unique), then the Postgre SQL-specific subclass will effect the updates using the table / UPDATE … To match on names we now need to match on two columns.

So whereas previously we specified the matching column using a scalar, when there is more than one column to match, we use an Array Ref.

(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.

If there is no database-specific subclass for the database in use, then will just use the base class which implements approaches that should work for any SQL database.

At the time of writing, the only database-specific subclass is for Postgre SQL. Let’s expand the original table a bit: “name” has now been split into “first_name” and “last_name”.

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.

OK, that’s great, we have some theoretical approaches for reducing the number of queries, now what?