Re: sql update

Mm. Is there some Cust# in Company 2 that doesn't exists in Company 1?

Try this:

Update Customer a
       set Customer_Store = (select 'C'||substr(Customer_Store, 1, n-1)
from Customer
                             where Cust#=a.Cust# and Company=1)
       where Company=2 and exists (select * from Customer where
Cust#=a.Cust# and Company=1)


Saludos,
                  ---------------------------
                    Santiago Martí
                       Dusen, S.A.
                  ---------------------------




                                                                           
             "Rowe, Sheri"                                                 
             <srowe@xxxxxxxxx>                                             
             Enviado por:                                             Para 
             rpg400-l-bounces@         <rpg400-l@xxxxxxxxxxxx>             
             midrange.com                                               cc 
                                                                           
                                                                    Asunto 
             15/02/2007 21:59          Re: sql update                      
                                                                           
                                                                           
                Por favor,                                                 
                responda a                                                 
              RPG programming                                              
              on the AS400 /                                               
                  iSeries                                                  
             <rpg400-l@midrang                                             
                  e.com>                                                   
                                                                           
                                                                           




Thanks Santiago,

Unfortunately, this did not work (tried it both ways).  I received an
error message:  Null values not allowed in column or variable CMSTOR.

I have since written a program as my Manager was getting perturbed.  It
just seemed like the perfect thing to do in SQL.  Thanks for your help
anyway.

Sheri

*************************************************
date: Thu, 15 Feb 2007 18:58:36 +0100
from: Santiago G Mart? <smarti@xxxxxxxx>
subject: Re: sql update

This should work:

Update Customer a
       set Customer_Store = (select 'C'||Customer_Store from Customer
where
Cust#=a.Cust# and Company=1)
       where Company=2

Perhaps you'd have to change it to: (select 'C'||substr(Customer_Store,
1,
n) from ...
where n is the length of field Customer_Store.


Saludos,
                  ---------------------------
                    Santiago Mart?
                       Dusen, S.A.
                  ---------------------------



--
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.