Re: SQL UPDATE of SELECTed records

["Steve McKay" <nospammers1@xxxxxxxxx>]

Thanks, Charles - your response got me on the right track.

Steve


"Wilt, Charles" <CWilt@xxxxxxxxxxxx> wrote in 
message 
news:F520B5C51DB10041B239BC06383A7EDC26DF07@xxxxxxxxxxxxxxxxxxxxxxxxxx
> 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.
>>
>>
>
> -- 
> 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.
>
>