Richard Teasdale
asked on
update one table using parameters from another
Hi: I have a delphi program where I want to, in English: "update all despatch records for customer X" where XXDESPATCH holds order records , GOORDERS holds order and customer records.
So far I have this code which updates a specific order in table XXDESPATCH:
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('UPDATE xxdespatch set despstatus= 1, scandate = :NOW where docno = :SELORD and despstatus = 0');
ADOQuery1.Parameters.Param ByName('NO W').Value := Now;
ADOQuery1.Parameters.Param ByName('SE LORD').Val ue := (Edit5.Text);
ADOQuery1.ExecSQL;
Now I need to change edit5.text to a customer code(done!) but have the parameter SELORD pointing at field AccNo in table GOORDERS, joined to XXDESPATCH at docno.
Can anybody help me along here?
Thanks!
So far I have this code which updates a specific order in table XXDESPATCH:
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('UPDATE xxdespatch set despstatus= 1, scandate = :NOW where docno = :SELORD and despstatus = 0');
ADOQuery1.Parameters.Param
ADOQuery1.Parameters.Param
ADOQuery1.ExecSQL;
Now I need to change edit5.text to a customer code(done!) but have the parameter SELORD pointing at field AccNo in table GOORDERS, joined to XXDESPATCH at docno.
Can anybody help me along here?
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much! Give you the points as first to reply but thanks to any other submissions, too!
update xxdespatch
set despstatus = 1, scandata = :now
where despstatus = 0
and docno in (select accno from goorders where customercode = :customercode)
this should update all the records for that customer
it's a little easier for us with some data of your tables
>> indicating what the source is and what the output should be