[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 478
  • Last Modified:

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.ParamByName('NOW').Value := Now;
ADOQuery1.Parameters.ParamByName('SELORD').Value := (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!
0
ClaytonGlass
Asked:
ClaytonGlass
1 Solution
 
Bhavesh ShahLead AnalysistCommented:
hi,

which field is common in both table?

UPDATE xxdespatch  
set despstatus= 1, scandate = :NOW 
from xxdespatch A, GOORDERS B
where <<a.commonfield>>=<<b.commonfield>>	 
and docno = :SELORD and despstatus = 0

Open in new window

0
 
Geert GruwezOracle dbaCommented:
basically you only need the query ?

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
0
 
ClaytonGlassAuthor Commented:
Thank you very much! Give you the points as first to reply but thanks to any other submissions, too!
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now