Updating fields in sql
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”.
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.
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.
This is in fact entirely possible in many database systems.In this case there are two distinct values, so we can do it in two UPDATE statements: So we can reduce the number of queries from five to two. But the extent to which this helps us reduces as the proportion of distinct SET values goes up.Eventually every SET value is different, for example: UPDATE staff SET salary = 1125 WHERE name = ' Bob'; UPDATE staff SET salary = 1200 WHERE name = ' Jane'; UPDATE staff SET salary = 1100 WHERE name = ' Frank'; UPDATE staff SET salary = 1175 WHERE name = ' Susan'; UPDATE staff SET salary = 1150 WHERE name = ' John'; Since every SET clause is now different, we can’t coalesce any queries using the approaches mentioned before, so we still have one UPDATE statement for every row requiring changes. What if we had a database table which contained a representation of the updates we wanted to make, e.g.“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”.
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.
Similarly, let’s say we want to update two fields, salary and bonus.