Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

update one table using parameters from another

Posted on 2013-10-23
3
Medium Priority
?
474 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

618 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