Subqueries and Correlated Updates

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

//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 subquery should not define the table primary in its FROM clause. The table primary is being updated in the RWDBUpdater and hence will be defined there. We indicate this by marking primary external to the subquery.

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

)