RE: Need help with SQL statement

Katherine,
Try replacing the right hand side of the SET= with your subquery. eg.
UPDATE FILEA 
      SET COST = (SELECT FILEB.COST * FROM FILEB WHERE FILEB.STOCK# =

           FILEA.STOCK#)
      WHERE VENDOR = '458182'

Cheers, Peter 
           

-----Original Message-----
From: Coleman, Katherine [mailto:KColeman@kmart.com]
Sent: Friday, July 14, 2000 11:28 AM
To: 'RPG400-L@midrange.com'
Subject: Need help with SQL statement


Cutting my teeth on some SQL - but may be way out in left field!!

Want to join table A to table B and update a column in table A with the
value of a column in table B.   I am stuck 'cuz it wont let me refernce a
field name from my SELECT subqry on the SET command in the update statement.
My redbook and my manual (I really tried to look this up!!) only have
examples where the SET statement is setting = to a constant (eg. COST =
10.00) or to a calculation (eg. COST = (COST * .10)).  It is spitting at me
on the SET statement and saying "Column qualifier or table FILEB undefined."
I am not doing this in a pgm yet.  Doing it online because I wanted to get
my syntax right first. 

Here is my statement - please dont laugh!! :-)
                                               

 UPDATE FILEA 
      SET COST = FILEB.COST 
      WHERE VENDOR = '458182' AND EXISTS     
          (SELECT * FROM FILEB WHERE FILEB.STOCK# =         
           FILEA.STOCK#)

+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator:
david@midrange.com
+---

**********************************************************************************************************
Privileged / Confidential Information may be contained in this message. If you 
are not the addressee indicated in this message (or responsible for delivery of 
the message to such person), you may not copy or deliver this message to 
anyone. In such case, you should destroy this message, and please notify us 
immediately. 

Please advise immediately if you or your employer does not consent to Internet 
e-mail for messages of this kind. Opinions and other information expressed in 
this message are not given or endorsed by my firm or employer unless otherwise 
indicated by an authorised representative independent of this message.
+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---