Re: SQL Update for multiple fields

Try the following
 update csship

 set fpl1tx, fpl2tx, fpl3tx, fpl5tx, fpoocd, fpcncd =
 b.ADDR1, b.SUITE, b.ADDR2, b.STATE, b.ZIP, b.COUNTRY
 from csship as a, qasship as b

 where   a.fpcomp = b.comp and

             a.fpjynb = b.cust and

             a.fpjxnb = b.ship
Now - please note that this is from memory and I believe there should be
some parentheses thrown in their for good measure but I THINK this is the
gist of what should be done.



Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx

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.