RE: SQL Update for multiple fields

Greg, try this:

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

And yes, left and right are types of outer joins, but irrelevant to the
update you're trying to do.

Elvis

Celebrating 10-Years of SQL Performance Excellence

-----Original Message-----
Subject: 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.