RE: Problem with SQL updating a field from a field in another file -- MIDRANGE-L

6,7, and 8 aren't getting updated because the statement execution is
halting on the error caused by 5 not existing.

You're probably seeing an error about NULL not being allowed.

There's plenty of examples in the archives, but you need:
1) To allow EMPNAM to be null
2) Assign some other value instead of NULL to #5
3) Don't try and update #5 at all.

You were trying to use option 2 above in your second statement:
update PROJECT A 
set A.EMPNAM = (select coalesce(B.NAME, A.EMPNAM) 
                from EMPMASTER B 
                where A.EMPCDE = B.CODE
                )


But it needs to be written like so:
update PROJECT A                              
set A.EMPNAM = coalesce((select B.NAME        
                          from EMPMASTER B       
                          where A.EMPCDE = B.CODE
                         )                      
                 , A.EMPNAM )                 


Option 3 looks like this:
update PROJECT A 
set A.EMPNAM = (select B.NAME 
                from EMPMASTER B 
                where A.EMPCDE = B.CODE
               )
Where A.EMPCODE in (Select C.CODE 
                    FROM EMPMASTER C
                    )


Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx 
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Valerio Vincenti
Sent: Thursday, October 19, 2006 3:55 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Problem with SQL updating a field from a field in 
another file

I'm running into a situation while using SQL to populate a 
field from a field in another file. I update the employee 
name on the Project file with the employee name from the 
Employee Master file (matched by the employee code). The SQL 
statement I'm using works fine if all the matches are 
satisfied. If the Project file has a record that doesn't 
match the Employee Master, then the update stops there and, 
even if there are other records in the Project file that 
match the Employee Master, they don't get updated.

Here's my SQL (with or without COALESCE I get the same results):

update PROJECT A set A.EMPNAM = (select B.NAME from EMPMASTER 
B where A.EMPCDE = B.CODE)

update PROJECT A set A.EMPNAM = (select coalesce(B.NAME, 
A.EMPNAM) from EMPMASTER B where A.EMPCDE = B.CODE)

Data in the Employee Master file (note that employee# 5 
doesn't exist):

CODE NAME   
1    MARK   
2    ROBERT 
3    VINCENT
4    JENNY  
6    KIM    
7    KAREN  
8    SONYA  

Data in the Project Master file before update:

EMPCDE EMPNAM 
1        
2       
3      
4        
5             
6             
7             
8             

Data in the Project Master after running the SQL:

EMPCDE EMPNAM 
1      MARK   
2      ROBERT 
3      VINCENT
4      JENNY  
5             
6             
7             
8             

Obviously the employee #5 doesn't get a match on the employee 
master, but I would expect the name of employees 6, 7 and 8 
to be populated.

Am I missing something? Is there a way to let SQL continue 
the processing after a failed match?

Thanks!

Valerio Vincenti
IT Business Analyst
County of Spotsylvania, I.S. Department
Spotsylvania, VA 22553
Phone (540) 507-7507
Fax (540) 582-9841
e-mail: vvincenti@xxxxxxxxxxxxxxxxxx 
-- 
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.