Using Update with new Batch function

[kneighbour]

I note with interest that there is now a new Batch function in UniDac. I use the Loader function quite a bit currently as it is so fast. This is limited to INSERTS, whereas the Batch function is not.

I looked at the blog entry on how to use the Batch function, focusing mostly on the Update section.

Most of my bulk update requirements are slightly different to the case shown in the blog, and I was wondering if there is some function in Unidac that will solve my problem? Or perhaps some clever SQL?

A typical scenario is that I want to update a single field in a table (eg Products) with the latest selling price value. ie a field might already have the value $5.00 in it. I want to increase that by 5%. ie update the value to NewValue = OldValue + (5/100 * OldValue)

Currently I simply have to loop through every record in a table, read in the old value, add the new data, then save the record back again.
ie

Code: Select all

  with tblProducts do
   while not EOF do
   begin
      OldSelling=FieldByName('SELL').AsFloat;
      NewSelling:=OldSelling + (5/100*OldSelling);
      if OldSelling<>NewSelling then
          begin
              edit;
              FieldByName('SELL').AsFloat:=NewSelling;
              post;
          end;
      next;
   end;

As you can imagine, this is terribly slow. Is there some way to use the Batch update system to perform this type of update?

I sometimes use the Script component with UPDATES ie
UPDATE PRODUCT SET SELL = SELL + (5/100 * SELL)
or something along those lines.

[ViktorV]

Batch update should be applied in case when you need to execute multiple updates in a table using the data generated in the client application. In your case, you want to update data in a table field using the information contained in the table. I.e., even using Batch update, you have to access all the records in the table to read the original data for Batch update execution. Therefore, in your case, the optimal approach to update the record is execution of your update query:

Code: Select all

UPDATE PRODUCT SET SELL = SELL + (5/100 * SELL)