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

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

-----Original Message-----
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