Multiple parameterized Delphi SQL updates within a transaction based on previous select query

Question

I am updating two different SQL tables in the same loop using parameterized queries in Delphi XE8. The whole thing is wrapped in a transaction, so that if anything in the loop fails, neither table gets updated.

My original query was posted here, here is the current simplified code after help from this site:

begin
  Query1:=TSQLQuery.Create(nil);
  try
    Query1.SQLConnection:=Connection;
    Query1.SQL.Text:='UPDATE TABLE A SET QUANTITY = :Quantity 
                      WHERE PRODUCT_NAME = :Product_name'; 

    Query2:=TSQLQuery.Create(nil);
    try
      Query2.SQLConnection:=Connection;
      Query2.SQL.Text:= 'UPDATE TABLE B SET QUANTITY = :Quantity 
                         WHERE PRODUCT_NAME = :Product_name'; 

      Transaction:=Connection.BeginTransaction;
      try
        for I := 1 to whatever to
        begin
          { fill params here and execute the commands }
          Query1.ExecSQL;
          Query2.ExecSQL;
        end;
        Connection.CommitFreeAndNil(Transaction);
      except
        Connection.RollbackFreeAndNil(Transaction);
        raise;
      end;
      .... etc.    

I've just realised that I might have a problem, in that part of the update of table B (ie query 2) involves first retrieving a record from table B, and then updating it based on one of the values returned.

So, if I flesh out the loop above:

for I:= 1 to whatever do
begin
  //Retrieve relevant values from file being read 
  Product_name:=Product_name[I]; 
  Quantity:=Value[I];

  //Execute query 1, no problems here 
  SQL_query1.Params.ParamByName('Product_name').AsString:=
                                                   Product_name;
  SQL_query1.Params.ParamByName('Quantity').AsString:=
                                                   Quantity;      
  Query1.ExecSQL;

  //Interim get from Table B 
  //I am using datasets here that are already open in my actual code, 
  //but it could also be a SQL_query3 component; I am simply showing 
  //the logic here of what's going on
  SQL_dataset1.CommandType:=ctQuery; 
  SQL_dataset1.CommandText:=
      'SELECT QUANTITY FROM TABLE B WHERE PRODUCT_NAME = '+Product_name;
  SQL_dataset1.Open; 

  Old_quantity:=SQL_dataset1.FieldByName('Quantity').AsString;

  New_quantity:=Old_quantity+Quantity;

  //Execute query 2
  SQL_query2.Params.ParamByName('Product_name').AsString:=
                                                   Trim_str(Product_name);
  SQL_query2.Params.ParamByName('Quantity').AsString:=
                                                   Trim_str(Quantity);   
  Query2.ExecSQL; 
  ... etc.
end;

So the entire loop could theoretically update the same product's quantity, and the updated quantity is based on the previous quantity.

Is this even possible, or will I have to settle for one update at a time? Sorry if this is a stupid question.

Also, while table A can certainly be updated using the above code as it doesn't have the same problem as table B, I don't want it to update at all if there are any issues with table B updates.

Part 2: simplified example

Table A is noise in what I'm actually trying to understand from this question, sorry, so let me rephrase with only table B. What's happening is that a file is being read sequentially, and based on each row's information, table B's running total has to be updated.

So let's say table B has the following records:

Product name Quantity
Red widget 3
Blue widget 5

We sequentially read in a file of widget purchases, which is in random order and contains a number of red and blue purchases mixed up.

For example:

1. Red widget +6
2. Red widget +2
3. Blue widget +1
4. Red widget +2 ... and so on.

Looking at the code sample below....

Query2.SQL.Text:= 'UPDATE TABLE B SET QUANTITY = :Quantity 
                   WHERE PRODUCT_NAME = :Product_name'; 

for I := 1 to file length do
begin
  //get the current quantity for that widget
  //add the quantity purchased in that row in the file to the 
  //quantity just retrieved 

  SQL_query2.Params.ParamByName('Product_name').AsString:=
                                                   Trim_str(Product_name);
  SQL_query2.Params.ParamByName('Quantity').AsString:=
                                                   Trim_str(Quantity); 
  Query2.ExecSQL;
end;

....my question is: will looping through a parameterized query like this update the running total as we go along? It's probably a stupid question, but just trying to wrap my head around the difference between that and this ...

for I := 1 to file length do
begin
  //get the current quantity for that widget
  //add the quantity purchased in that row in the file to the  
  //quantity just retrieved 

  Query2.SQL.Text:= 'UPDATE TABLE B SET QUANTITY = ' + Quantity + 
                    'WHERE PRODUCT_NAME = 'Red widget'; 
  Query2.ExecSQL;
end; 

...where it certainly is updating as you go along. Just want to be sure that I understand these parameterized queries properly. From what I've read, there certainly seems to be some optimization if you use parameters? I think where I'm not clear/correct is my impression that the 'optimization' when using parameters doesn't mean fewer trips to the database? Fewer calls to the database = running totals going out of sync, as a question in my head!

Obviously the tables and files are more complex than this, and we have ID's set up as keys etc. My bad example is just for the purposes of the logic of the question. Once I understand this, I can apply my limited knowledge to improving the query!

Answer 1

As a guess on what you're attempting to do, I think the following SQL approach is better than your attempt at looping through updates. I am basing this on table A containing a list of quantity changes and table B containing the current quantity (i.e. if table B has 3 foo, 2 bar and table A has +2 foo, -1 foo, +1 bar the result after the operation will be table B having 4 foo and 3 bar)

UPDATE TableA
SET Quantity = TableA.Quantity + 
   (SELECT sum(tableB.Change)
    FROM TableB
    WHERE TableA.ID = TableB.ID)

This works in Fiddle for SQL Server, YMMV (http://sqlfiddle.com/#!6/017df/7/0)

As an aside, you may want to join on a ProductID primary key rather than product name. If you want to understand why then look into DB normalisation

To cover your actual question, I can't see any reason that it wouldn't work (it would just be a LOT slower than the single SQL statement above). The transaction effectively 'freezes' any records you touch with your UPDATE statements. You'll be able to use (SELECT and UPDATE) any manipulated records as though there's no transaction, but others may or may not be able to see them depending on other DB settings (and definitely can't update/delete them), so as long as you don't run your other queries in a separate SQLConnection you'll be fine.

Amendment following question edit:

Yes, that should work fine, though I highly recommend doing

UPDATE TableB 
SET Quantity = Quantity + :QuantityChange 
WHERE PRODUCT_NAME = :Product_name

then you don't need to run the other SELECT query (unless you need the updated total client-side for instance in writing out a log). The big bonus with parameters is protection from SQL injection attacks, but it can also help with the query optimisation at the DB end. In terms of trips to the DB you get one of those every EXECUTE, the optimisation just means that the DB spends less time thinking about it each time.