RE: Need help with SQL statement

The Scalar Subselect for SQL - V4R3 will allow you to update a field in one
file based on the value from a field in another file.  In the following
example (from the March 1999 issue of AS/400 Experts Journal), the ABTAX
field for a given ITEMNUMBER in the ITEMMASTER file is updated based on the
NEWQTY found for that ITEMNUMBER in the ADJUSTMENT file

        update ItemMaster I
                SET OnHand = 
                (SELECT NewQty FROM  Adjustment A
                WHERE I.ItemNumber = A.ItemNumber)

The following example from the August 1999 Midrange Computing uses different
syntax.  It updates three fields.
        update eric_test/itemmaster
          set (ABTAX, ABALPH, ABDC)  =
          (select ABTAX, ABALPH, ABDC from eric_test/adjustment
           where  itemmaster.ABAN8 =  adjustment.aban8)

Eric Lehti


-----Original Message-----
From: Coleman, Katherine [mailto:KColeman@kmart.com]
Sent: Thursday, July 13, 2000 6:28 PM
To: 'RPG400-L@midrange.com'
Subject: Need help with SQL statement

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. 

 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
+---