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.
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 can be performed on RWDBUpdater in a similar fashion to RWDBSelector. Please refer to Section 5.3.8, "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
|
| //1 | Creates an instance of RWDBSelector defining the subquery that fetches data from table backup. |
| //2 | Creates an RWDBUpdater instance to update the table primary. |
| //3 | Defines the assignments in the SET clause. |
| //4 | Defines a RWDBCriterion which takes the subquery as an expression. |
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);
|
| //1 | These four lines define the correlated subquery by referencing a column from table primary in the where condition. The table primary is being updated in the RWDBUpdater and hence will be defined there. The subquery should not define the table primary in its FROM clause. We indicate this by marking primary external to the subquery on //4. |
| //2 | The next three lines define the RWDBUpdater in the same way as the previous example. |
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
)
|
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.
Correlated updates explained in Section 5.5.1 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. In the above example:
RWDBTable primary = myDbase.table("primary");
primary.tag("");
|
... applies only to Access Modules that do not support a table tag in RWDBUpdater.
Copyright © Rogue Wave Software, Inc. All Rights Reserved.
The Rogue Wave name and logo, and SourcePro, are registered trademarks of Rogue Wave Software. All other trademarks are the property of their respective owners.
Contact Rogue Wave about documentation or support issues.