The SET clause then takes the “salary” field from the “updates” table and uses it to update the “salary” field of the “staff” table.We can easily contrive for an “updates” table to exist by creating a temporary table and populating it.
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”.
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.
Similarly, let’s say we want to update two fields, salary and bonus.
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.
“value_columns” specifies the columns which will be updated (using SET). The second element provides the value to be SET in the column specified by “value_columns”.
This is in fact entirely possible in many database systems.
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.
For small numbers of rows requiring updates, it can be adequate to use an UPDATE statement for each row that requires an update.
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.
In this case, will spot that the target values are all 1200, and will effect the desired changes using a single UPDATE statement as described above.