?
Solved

update one table using parameters from another

Posted on 2013-10-23
3
Medium Priority
?
470 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
[X]
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
3 Comments
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 1000 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 38

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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

777 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