Subqueries and Correlated Deletes

SourcePro : DB Interface Module User’s Guide : PART II Using the Basic Features : The Data Manipulation Classes : Deleting Data : 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

//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

)