• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 480
  • 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
Richard Teasdale
Asked:
Richard Teasdale
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 GOracle 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
 
Richard TeasdaleFinancial ControllerAuthor Commented:
Thank you very much! Give you the points as first to reply but thanks to any other submissions, too!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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