RE: SQL Update for multiple fields

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.