A requirement arises in many systems to update multiple SQL database rows.
Similarly, let’s say we want to update two fields, salary and bonus.
If we want to update more than one column, whereas previously we specified the new value using a scalar, when there is more than one column to update, we use an Array Ref.
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”.
and we could persuade the database server to apply those updates to the target table?
This is in fact entirely possible in many database systems.
So is structured so that approaches which are generic across different SQL databases are expressed in a base class, and approaches which only work for specific SQL databases are expressed in a subclass.
An object of the relevant class is instantiated when the call is made, and control then passed to the implementation relevant to the database in use.
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.
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.