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

i updating 2 different sql tables in same loop using parameterized queries in delphi xe8. whole thing wrapped in transaction, if in loop fails, neither table gets updated.

my original query posted here, here current simplified code after site:

begin   query1:=tsqlquery.create(nil);   try     query1.sqlconnection:=connection;     query1.sql.text:='update table set quantity = :quantity                        product_name = :product_name';       query2:=tsqlquery.create(nil);     try       query2.sqlconnection:=connection;       query2.sql.text:= 'update table b set quantity = :quantity                           product_name = :product_name';         transaction:=connection.begintransaction;       try         := 1 whatever         begin           { fill params here , execute commands }           query1.execsql;           query2.execsql;         end;         connection.commitfreeandnil(transaction);       except         connection.rollbackfreeandnil(transaction);         raise;       end;       .... etc.     

i've realised might have problem, in part of update of table b (ie query 2) involves first retrieving record table b, , updating based on 1 of values returned.

so, if flesh out loop above:

for i:= 1 whatever begin   //retrieve relevant values 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 table b    //i using datasets here open in actual code,    //but sql_query3 component; showing    //the logic here of what's going on   sql_dataset1.commandtype:=ctquery;    sql_dataset1.commandtext:=       'select quantity table b 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 entire loop theoretically update same product's quantity, , updated quantity based on previous quantity.

is possible, or have settle 1 update @ time? sorry if stupid question.

also, while table can updated using above code doesn't have same problem table b, don't want update @ if there issues table b updates.

part 2: simplified example

table noise in i'm trying understand question, sorry, let me rephrase table b. what's happening file being read sequentially, , based on each row's information, table b's running total has updated.

so let's table b has following records:

product name quantity
red widget 3
blue widget 5

we sequentially read in file of widget purchases, in random order , contains number of red , blue purchases mixed up.

for example:

1. red widget +6
2. red widget +2
3. blue widget +1
4. red widget +2 ... , on.

looking @ code sample below....

query2.sql.text:= 'update table b set quantity = :quantity                     product_name = :product_name';   := 1 file length begin   //get current quantity widget   //add quantity purchased in row in file    //quantity 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: looping through parameterized query update running total go along? it's stupid question, trying wrap head around difference between , ...

for := 1 file length begin   //get current quantity widget   //add quantity purchased in row in file     //quantity retrieved     query2.sql.text:= 'update table b set quantity = ' + quantity +                      'where product_name = 'red widget';    query2.execsql; end;  

...where is updating go along. want sure understand these parameterized queries properly. i've read, there seems optimization if use parameters? think i'm not clear/correct impression 'optimization' when using parameters doesn't mean fewer trips database? fewer calls database = running totals going out of sync, question in head!

obviously tables , files more complex this, , have id's set keys etc. bad example purposes of logic of question. once understand this, can apply limited knowledge improving query!

as guess on you're attempting do, think following sql approach better attempt @ looping through updates. basing on table containing list of quantity changes , table b containing current quantity (i.e. if table b has 3 foo, 2 bar , table has +2 foo, -1 foo, +1 bar result after operation table b having 4 foo , 3 bar)

update tablea set quantity = tablea.quantity +     (select sum(tableb.change)     tableb     tablea.id = tableb.id) 

this works in fiddle sql server, ymmv (http://sqlfiddle.com/#!6/017df/7/0)

as aside, may want join on productid primary key rather product name. if want understand why db normalisation

to cover actual question, can't see reason wouldn't work (it lot slower single sql statement above). transaction 'freezes' records touch update statements. you'll able use (select , update) manipulated records though there's no transaction, others may or may not able see them depending on other db settings (and can't update/delete them), long don't run other queries in separate sqlconnection you'll fine.

amendment following question edit:

yes, should work fine, though highly recommend doing

update tableb  set quantity = quantity + :quantitychange  product_name = :product_name 

then don't need run other select query (unless need updated total client-side instance in writing out log). big bonus parameters protection sql injection attacks, can query optimisation @ db end. in terms of trips db 1 of every execute, optimisation means db spends less time thinking each time.