Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

update one table using parameters from another

Posted on 2013-10-23
3
456 Views
Last Modified: 2013-10-23
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
Comment
Question by:ClaytonGlass
3 Comments
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 250 total points
ID: 39593765
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
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 39593771
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
 

Author Closing Comment

by:ClaytonGlass
ID: 39593810
Thank you very much! Give you the points as first to reply but thanks to any other submissions, too!
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question