Intermediate:Update SQL question

Hi..All,

   I have a question. There are 2 tables, employee (Master) & employee_tmp (temp table).

When I execute this statement :

update employee a
set (a.manager_id, a.name, a.address, a.zip_code) =
(select b.manager_id, b.name, b.address, b.zip_code
from employee_tmp b
where a.emp_no = b.emp_no
and b.manager_id in (select emp_no from employee) );

Note : employee.Manager_id has a constraint which refers back to employee.emp_no; The stmt
"and b.manager_id in (select emp_no from employee)" is added coz, sometimes we get junk id's & I don't want to stop the rest of the update due to some invalid id's.

It gives me an error that "Cannot update zip_code to NULL".  Name, address & zip_code in the master table are "Not NULL". I have verified that the tmp tables also doesn't have NULL.

But when I add another clause i.e.

       update employee a
set (a.name, a.address, a.zip_code) =
(select b.name, b.address, b.zip_code

from employee_tmp b
where a.emp_no = b.emp_no
and b.manager_id in (select emp_no from employee) )
and a.emp_no in (select emp_no from employee_tmp);

It works. I thought the 1st SQL was sufficient to do the update. Can someone please explain me where am I going wrong. Also is it possible to re-write the query.?

TIA

--


Regards,

Praveen