Deleting Data
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.
NOTE >> 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
Subqueries and correlated deletes can be performed on
RWDBDeleter in a similar fashion to
RWDBSelector. Please refer to
“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
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);
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
)
Use of Table Tags when Deleting
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
“Subqueries and Correlated Deletes”, 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("");