RE: SQL UPDATE of SELECTed records

["Wilt, Charles" <CWilt@xxxxxxxxxxxx>]

Steve,

You're example code isn't enough.  As other have mentioned the where clause 
doesn't make sense.

This would work fine:

UPDATE library/filea A
SET field1 = 'U'
WHERE
a.field2 in (SELECT B.field1
             FROM library/fileb B
             WHERE conditions)

or for instance (one I just did yesterday),

update mpwipimay2 A                        
set(A.WIIINA, A.WIIOTA, A.WIIYRI, A.WIIYRO) =  ( select  B.WIIINA,    
                                                         B.WIIOTA,
                                                         B.WIIYRI,
                                                         B.WIIYRO 
                                                 from mpwipimay1 B  
                                                 where A.WIIPRT = B.WIIPRT  
                                                   and A.WIICLS = B.WIICLS)

HTH, 

Charles Wilt
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Steve McKay
> Sent: Tuesday, March 01, 2005 11:14 AM
> To: rpg400-l@xxxxxxxxxxxx
> Subject: SQL UPDATE of SELECTed records
> 
> 
> All -
> 
> My apologies for posting in the RPG group - I didn't find an 
> SQL group.
> 
> I am trying to UPDATE several records returned by a SELECT 
> statement but am 
> getting SQL0104 (Token <END-OF-STATEMENT> was not valid. 
> Valid tokens: < > = 
> <> <= !<).
> 
> Can this be done?  Current statement looks like this:
> 
> UPDATE library/file
> SET field = 'U'
> WHERE
> (SELECT fields
> FROM library/file
> WHERE conditions)
> 
> Obviously, I've left out actual library and field names plus 
> the conditions 
> that I'm testing, but you get the idea.
> 
> Thanks in advance,
> 
> Steve 
> 
> 
> 
> -- 
> This is the RPG programming on the AS400 / iSeries (RPG400-L) 
> mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
> 
>