updating a value with several conditions

Question

I am trying to add several condition. I'd like to update base2 with sum of itself with an intermediate value, and I'd like to post some conditions on intermediate value and base2.

I modified the table manually in database. Intermediatevalue is one of the columns in the table, and is calculated based on the base2 value, In the first row, I have a base2 value, and I calculate to get first row intermediate value, now in the second row, I need to set the new base2=previous base2+previous intermediate value. That is why I have two counters to trace where the item's positions are. Counter1 counts the index of itemid, and counter2 traces counts for the loop inside the itemid

The question is how to set this new base2. Is it possible to set my new base2 on one line? Or will I have to set another variable to the intermediate value in the previous row and add it as a new variable to base2?

Here below is what I want to have, but has errors (function missing) ).

UPDATE TABLE2 SET base2=   
    (base2+INTERMEDIATEVALUE WHERE loadingordinal=counter2 AND itemid=counter1)    
WHERE loadingordinal=counter2 +1 AND itemid=counter1

Answer 1

In VFP, doing such updates via Update_SQL is hard. Fortunately there are many ways to solve the problem and one is to use the xBase command instead (in xBase you can use REPLACE for SQL-UPDATE. Replace operates on "current row" by default (no scope clause is included). You didn't supply any schema nor any sample data so I will give only a plain sample by guess. Your code would look like:

local lnPrevious, lnItemId
lnItemId = -1
Select Table2
Scan
      && Save Current Id (ItemID?)
      lnItemId = Table2.ItemID
      && Do calculation using previous value
      && On first row it is the same as base2
      lnPrevious = base2
      scan While Table2.ItemId = m.lnItemId
          Replace Base2 with m.lnPrevious, ;
                  IntermediateValue with DoCalculation(Base2)
          && Previous value for next row
          lnPrevious = Base2 + IntermediateValue    
      endscan   
      skip -1 && Endscan would move the pointer
Endscan

Note, if you need more than ItemId (or maybe passing Base2 and IntermediateValue itself rather than lnPrevious) you could as well do something like:

local loRow
scan 
   scatter to name loRow memo
   scan while table2.ItemId = m.loRow.ItemId
   ...