How to avoid columns with total values (summarized from multiple rows)

checkmail wrote:

I need to split an input string and import some data in tables, like this (simplified) '123423453456'

In this case, the value 1 is 1234, value 2 = 2345. With a while loop the values were inserted, if I get an error (value is not a integer), then I will delete all records with this import-id.

Now, value 1 should be add to an total counter, f. e. old value = 10000, new = 11234. If I get an error, I would like to return to the old value. Now I have the following idea, I create a new column named tmp_insert an enter the value to add. If all records were inserted without any errors, I can add the new value to the total one and reset the column to 0. Now my question, how handled firebird this with concurrency, if I call the stored procedure more than once a time. Will ervery step worked to the end before the new call runs? Can it be problematic? (column tmp_insert and add to the total value)

Svein Erling Tysvær answers:

Having one record with a total value is almost the same as doing things serial rather than parallel, so avoid this if concurrency is an issue.

Rather than changing the value from 10000 to 11234, insert a new record into the summation table with the value 1234. And rather than subtracting upon deletes, insert a new record with a negative value. To get the total value, query sum(TotalCounter) rather than just TotalCounter.

Every once in a while (typically in a batch run during the night), do something like (I've added a group field, often you want to store more than one value - feel free to use a stored procedure rather than execute block).

EXECUTE BLOCK as
  declare variable SomeGroup integer;
  declare variable NewSum integer;
begin
  for select MyGroup, sum(TotalCounter)
      from MyTable
      group by MyGroup
  into :SomeGroup, :NewSum do
  begin
    delete from MyTable
    where MyGroup = :SomeGroup;
    insert into MyTable(MyGroup, TotalCounter)
    values(:SomeGroup, :NewSum);
  end
end

No need for any tmp_insert column.