How can the following while code be optimized? (Modify the existing quantity in the inventory table after storage)

roger12

// Modify the existing quantity in the inventory table
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add ('Select * from RukuTmp');
ADOQuery1.Open;
// Find all data from the temporary table
ADOQuery1.First;
while not ADOQuery1.Eof do
begin
  ADOQuery2.Close;
  ADOQuery2.SQL.Clear;
  ADOQuery2.SQL.Add ('Select * from Kucun where ChID =: a');
  ADOQuery2.Parameters.ParamByName ('a'). Value: = ADOQuery1.fieldbyname ('ChID'). AsString;
  ADOQuery2.Open;
  ADOQuery2.Edit;
  ADOQuery2.FieldByName ('Kcsl'). AsFloat: = ADOQuery2.fieldbyname ('Kcsl'). AsFloat
    + ADOQuery1.fieldbyname ('Rksl'). AsFloat;
  ADOQuery2.Post;
  ADOQuery1.Next;
end;

roger12

Can I modify the data in the inventory table at one time without using a while loop?

xclzc17

update Ku village set KC SL = Ku village.KC SL + Ru library tmp.RK died from Ku village, Ru library tmp where Ku village. CH ID = Ru library tmp.CH ID

roger12

Both the inventory quantity and inventory amount can be changed like this
update Kucun
set Kcsl = Kucun.Kcsl + RukuTmp.Rksl, Kcje = Kucun.Kcje + RukuTmp.Rkje
from Kucun, RukuTmp
where Kucun.ChID = RukuTmp.ChID

But what about the unit price of inventory?
The inventory unit price is obtained by dividing the inventory amount by the inventory quantity.
update Kucun set Kcdj = Kcje / Kcsl from Kucun, RukuTmp
where Kucun.ChID = RukuTmp.ChID and Kucun.Kcsl <> 0 (inventory quantity is not 0)
update Kucun set Kcdj = 0 from Kucun, RukuTmp
where Kucun.ChID = RukuTmp.ChID and Kucun.Kcsl = 0 (the stock quantity is 0)

四月天

Sum with SUM
with ADOQuery1 do
begin
  close;
  sql.clear;
  sql.add ('update Kucun set Kcsl = c.Kcsl from (select ChID, sum (Kcsl) as Kcsl from RukuTmp group by ChID) c, warehouse where Kucun.ChID = c.ChID');
  execsql;
end;

roger12

xclzc17()
I tried, your method is wrong, you cannot join two tables after Update.

xclzc17

What database? What does it mean that two tables cannot be joined?

roger12

I am an Access database, and I am prompted at runtime:
Syntax error (operator missing) in query expression 'Kucun.Kcsl + RukuTmp.Rksl from Kucun'.

roger12

So I judge it is ', RukuTmp' is not recognized by the program, can I only follow one table after the update?

xclzc17

Well, it's possible that Access does not support this SQL syntax. You can try the四月天method.

If it doesn't work, you can use a loop
ADOQuery1.Open;
// Find all data from the temporary table
ADOQuery1.DisableControls; // Add this sentence
try
ADOQuery1.First;
ADOQuery2.Close;
ADOQuery2.SQL.Clear;
ADOQuery2.SQL.Add ('update Kucun set Kcsl = Kcsl +: Rksl where ChID =: ChID');
while not ADOQuery1.Eof do
begin
  ADOQuery2.Parameters.ParamByName ('Rksl'). Value: = ADOQuery1.fieldbyname ('Rksl'). AsFloat;
ADOQuery2.Parameters.ParamByName ('ChID'). Value: = ADOQuery1.fieldbyname ('ChID'). AsString;
ADOQuery2.ExecSQL

  ADOQuery1.Next;
end;
finally
ADOQuery1.EnableControls;
end;