× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Elvis,

Funny you should mention that.  I have it saved on my computer from the
first time Birgitta posted it.  Although this article uses examples of
updating a single field using a subquery, I was thinking it looks rather
awkward when updating several fields to use a subquery to the same file
on every field.  But I'm thinking that is what I have to do, like this:

update csship
set fpl1tx = (select ADDR1 from qasship 
              where comp = fpcomp and
                    cust = fpjynb and
                    ship = fplxnb),
        fpl2tx = (select SUITE from qasship 
              where comp = fpcomp and
                    cust = fpjynb and
                    ship = fplxnb),
        fpl3tx = (select ADDR2 from qasship 
              where comp = fpcomp and
                    cust = fpjynb and
                    ship = fplxnb),
        fpl5tx = (select CITY from qasship 
              where comp = fpcomp and
                    cust = fpjynb and
                    ship = fplxnb),
        fpoocd = (select STATE from qasship 
              where comp = fpcomp and
                    cust = fpjynb and
                    ship = fplxnb),
        fpcncd = (select COUNTRY from qasship 
              where comp = fpcomp and
                    cust = fpjynb and
                    ship = fplxnb)

Rob suggested I look for "left inner join" in the archives, but I didn't
get any recent hits.  Also, I may be revealing my SQL ignorance here,
but I was under the impression that "left" and "right" were types of
outer joins, rendering the expression "left inner join" oxymoronic.
Perhaps Rob meant for me to look for "left outer join" in the archives,
or just "left joins" ?

Tried Alan's suggestion (looked real promising), but no joy there, no
matter how I play with the parentheses.  

Thanks to all anyhow. 


|-----Original Message-----
|From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
|bounces@xxxxxxxxxxxx] On Behalf Of Elvis Budimlic
|Sent: Tuesday, March 20, 2007 11:31 AM
|To: 'Midrange Systems Technical Discussion'
|Subject: RE: SQL Update for multiple fields
|
|Check this thread:
|
|http://archive.midrange.com/rpg400-l/200702/msg00415.html
|
|Celebrating 10-Years of SQL Performance Excellence
|
|-----Original Message-----
| Subject: SQL Update for multiple fields
|
|I wanted to do this, which is acceptable in TSQL, but apparently not on
|the iSeries:
|
|
|
|update csship
|
|set fpl1tx = b.ADDR1,
|
|      fpl2tx = b.SUITE,
|
|      fpl3tx = b.ADDR2,
|
|      fpl5tx = b.STATE,
|
|      fpoocd = b.ZIP,
|
|      fpcncd = b.COUNTRY
|
|from csship as a, qasship as b
|
|where   a.fpcomp = b.comp and
|
|            a.fpjynb = b.cust and
|
|            a.fpjxnb = b.ship
|
|
|
|Is my only alternative to do a subquery for each one of the fields ?
|
|Seems like there ought to be a simpler way.
|
|Let me know if I'm missing something.
|
|Thanks
|
|
|
|Greg Fleming
|
|
|--
|This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
|To post a message email: MIDRANGE-L@xxxxxxxxxxxx
|To subscribe, unsubscribe, or change list options,
|visit: http://lists.midrange.com/mailman/listinfo/midrange-l
|or email: MIDRANGE-L-request@xxxxxxxxxxxx
|Before posting, please take a moment to review the archives
|at http://archive.midrange.com/midrange-l.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.