Using update_recordset with Views

We know that update_recordset statements should be used in place of while select forupdate wherever we can. We get the benefit of time saved by reduced server-sql trips.

Lets discuss a unique scenario.
Imagine you have to update table A with a status based on count of a particular column on another related table B. While select would look like this.

   ttsbegin;  
   while select forupdate stagingImportTable  
   exists join stagingPurchImport  
   where stagingPurchImport.ImportId == stagingImportTable.RecId  
   exists join stagingImportQueue  
   where stagingImportQueue.ImportID == stagingImportTable.ImportId &&  
   stagingImportQueue.ToDelete  
   {  
     select count(RecId) from stagingPurchImport
       where stagingPurchImport.ImportId == stagingImportTable.RecId &&  
       !stagingPurchImport.PostedId;  
     if (stagingPurchImport.RecId != 0)  
       stagingImportTable.Status = MMSImportStatus::Incomplete;  
     else  
       stagingImportTable.Status = MMSImportStatus::Posted;  
     stagingImportTable.update();  
   }  
   ttscommit;  

Essentially we need to know the count (or any other aggregate value for that matter) of a column to decide which status needs to be set. The above statement can be very time consuming and that's where Views come to the rescue. How do we go about it? First we need a query with table B as primary datasource and range as the column we need the count of, joined by table A. See below image.

Now we create a View with the above query as datasource (just drag and drop) and add two fields. First one we call CountOfRecId, which is an aggregation of RecIds. See below. (Note: when you try adding the field to a view, by default its a string, but when you choose the actual field, the datatype changes). Second field we add (ImportID) is to assist the join in the update statement we see next.

     ttsbegin;  
     update_recordset stagingImportTable  
     setting Status = MMSImportStatus::Incomplete  
     exists join purchView  
     where purchView.CountOfRecId != 0 &&  
     purchView.ImportId == stagingImportTable.ImportId  
     exists join stagingImportQueue  
     where stagingImportQueue.ImportID == stagingImportTable.ImportId &&  
     stagingImportQueue.ToDelete;  
     ttscommit;  
     ttsbegin;  
     update_recordset stagingImportTable  
     setting Status = MMSImportStatus::Posted  
     exists join stagingImportQueue  
     where stagingImportQueue.ImportID == stagingImportTable.ImportId &&  
     stagingImportQueue.ToDelete  
     notexists join purchView  
     where purchView.ImportId == stagingImportTable.ImportId;  
     ttscommit;  

Since we have leveraged the power of queries and views, we get the count super-fast, which we then use in the update_recordset statement by making a join with the View, which behaves like a table. If you browse the View, it looks like this. Note the RecId value.