Updating Data
Class
RWDBUpdater is used to update database tables. An updater is obtained by requesting one from a database table, and it may be supplied with an
RWDBCriterion to express a restriction on the rows to be updated. You express a restriction by using a
where() method, just as with
RWDBSelector.
An updater must be told which columns to update, and which new values to use. You do this by supplying the updater with one or more
RWDBAssignment instances. These
RWDBAssignment instances are created anonymously when you call the
assign() method of
RWDBColumn. The insertion operator
<< is used to add the assignments to an updater:
int videoID = 1234;
int supplierID = 11;
int quantity = 12;
RWDBTable purchases = myDbase.table("purchase");
RWDBUpdater update = purchases.updater(); //1
update << purchases["supplierID"].assign(supplierID) //2
<< purchases["quantity"].assign(quantity); //3
update.where(purchases["videoID"] == videoID); //4
update.execute(myConnection); //5
On
//1, an
RWDBUpdater is obtained for the
purchase table. Lines
//2 and
//3 add two
RWDBAssignment instances to the updater anonymously, and
//4 sets the
WHERE clause. The result is equivalent to an SQL statement like:
UPDATE purchases
SET supplierID = 11, quantity = 12
WHERE videoID = 1234
When the
execute() method is invoked on
//5, the
purchase table in the database is updated. As discussed with regard to
RWDBInserter, the
execute() method returns an
RWDBResult, which may be ignored.
Note the importance of the where() call on //4. If this were omitted, no restriction would be placed on the update, resulting in an update of all rows within the purchase table.
NOTE >> Executing an updater without first assigning a criterion causes all rows in the table to be updated.
Once a criterion has been assigned to an updater using the where() method, that criterion is retained. The where() method can be called at any time to set a new criterion, or it can be called with an empty criterion to clear the criterion.
As with
RWDBInserter, we recommend using an explicit connection when executing an update.
Subqueries and Correlated Updates
Subqueries and correlated updates can be performed on
RWDBUpdater in a similar fashion to
RWDBSelector. Please refer to
“Subqueries” for the details of constructing subqueries and correlated subqueries.
Following is an example of use of a subquery in an
RWDBUpdater.
RWDBTable primary = myDbase.table("primary");
RWDBTable backup = myDbase.table("backup");
RWDBSelector avg = myDbase.selector(); //1
avg << rwdbAvg(backup["onHand"]);
RWDBUpdater update = primary.updater(); //2
update << primary["notes"].assign("Note A"); //3
update.where(primary["onHand"] >= avg); //4
A reasonable interpretation of this code fragment could be expressed this way in SQL:
UPDATE primary
SET primary.notes = 'Note A'
WHERE primary.onHand >=
(
SELECT AVG(backup.onHand)
FROM backup
)
Following is an example of correlated update by use of a correlated subquery in an
RWDBUpdater.
RWDBTable primary = myDbase.table("primary");
RWDBTable backup = myDbase.table("backup");
RWDBSelector avg = myDbase.selector(); //1
avg << rwdbAvg(backup["onHand"]);
avg.where(backup["ID"] == primary["ID"]);
avg.fromExtern(primary);
RWDBUpdater update = primary.updater(); //2
update << primary["notes"].assign("Note B");
update.where(primary["onHand"] >= avg);
A reasonable interpretation of this code fragment could be expressed this way in SQL:
UPDATE primary
SET primary.notes = 'Note B'
WHERE primary.onHand >=
(
SELECT AVG(backup.onHand)
FROM backup
WHERE backup.ID = primary.ID
)
Use of Table Tags when Updating
Not all databases support use of table tags in
UPDATE SQL statements. Each DB Access Module takes care of this internally so that
RWDBUpdater produces a
UPDATE SQL with or without table tags, depending on the module. The use of table tags by each of the Access Modules is documented in the Access Module User's Guides in the section on updaters and deleters.
Correlated updates explained in
“Subqueries and Correlated Updates” contain subqueries that reference the table being updated. If a particular Access Module does not support the use of table tags in
RWDBUpdater, the column name in the subquery that references the table being updated may still get qualified using the table tag. This can be prevented by clearing the tag on the table being updated, like this:
RWDBTable primary = myDbase.table("primary");
primary.tag("");