Class RWDBDeleter is used to delete rows from database tables. A deleter 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 deleted. You can express a restriction through a where() method, just as with RWDBSelector and RWDBUpdater. The structure should be familiar by now:
int videoID = 1234;
RWDBTable purchases = myDbase.table("purchase");
RWDBDeleter deleter = purchases.deleter();
deleter.where(purchases["videoID"] == videoID); //1
deleter.execute(myConnection); //2
|
This example is equivalent to an SQL statement of this type:
DELETE FROM purchase WHERE videoID = 1234 |
It is executed on //2, deleting the rows in the purchase table whose videoID column contains the value 1234. As with RWDBUpdater, the where() call on //2 is important. Omitting it would have caused all rows in the purchase table to be deleted. Executing a deleter without calling the where() method, which assigns a criterion, causes all rows within the table to be deleted.
Executing a deleter without first assigning a criterion causes all rows of the table to be deleted.
As with an updater, once a criterion has been assigned to a deleter using the where() method, that criterion is retained. The where() method can be called at anytime to set a new criterion, or it can be called with an empty criterion to clear the criterion. The considerations regarding results and connections for RWDBInserter and RWDBUpdater apply equally to RWDBDeleter.
Subqueries and correlated deletes can be performed on RWDBDeleter 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 using a subquery in an RWDBDeleter
RWDBTable primary = myDbase.table("primary");
RWDBTable backup = myDbase.table("backup");
RWDBSelector avg = myDbase.selector(); //1
avg << rwdbAvg(backup["onHand"]);
RWDBDeleter delete = primary.deleter(); //2
delete.where(primary["onHand"] >= avg); //3
|
| //1 | These two lines create an instance of RWDBSelector that defines the subquery that fetches data from table backup. |
| //2 | Creates an RWDBDeleter instance to delete rows from the table primary. |
| //3 | Defines a RWDBCriterion which takes the subquery as an expression. |
A reasonable interpretation of this code fragment could be expressed this way in SQL:
DELETE FROM primary
WHERE primary.onHand >=
(
SELECT AVG(backup.onHand)
FROM backup
)
|
Following is an example of correlated delete by use of a correlated subquery in an RWDBDeleter.
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);
RWDBDeleter delete = primary.deleter(); //2
delete.where(primary["onHand"] >= avg);
|
| //1 | The first four lines define the correlated subquery by referencing a column from table primary in the WHERE condition. The subquery should not define the table primary in its FROM clause since it is being modified in the RWDBDeleter and hence will be defined there. We indicate this by marking primary external. |
| //2 | These two lines define the RWDBDeleter the same way as in the previous example. |
A reasonable interpretation of this code fragment could be expressed this way in SQL:
DELETE FROM primary
WHERE primary.onHand >=
(
SELECT AVG(backup.onHand)
FROM backup
WHERE backup.ID = primary.ID
)
|
Not all databases support use of table tags in DELETE SQL statements. DB Access Modules for databases take care of this internally so that RWDBDeleter produces a DELETE SQL with or without table tags, depending on that particular Access Module. The use of table tags by each of the Access Modules is documented in the Access Module User's Guides.
Correlated deletes, explained in Section 5.6.1, contain subqueries that reference the table being updated. If a particular Access Module does not support use of table tags in RWDBDeleter, the column name in the correlated subquery referencing the table being modified may still get qualified by using the table tag. This can be prevented by clearing the tag on the table being modified. In the above example:
RWDBTable primary = myDbase.table("primary");
primary.tag("");
|
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.
Provide feedback to Rogue Wave about its documentation.