Re: Intermediate:Update SQL question

Praveen <pmohanan_at_directvinternet.com> wrote in message news:<3CB290E0.D550E7BE_at_directvinternet.com>...
> 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.

So how do you verify that employees that do get updated are not changed to the wrong manager??

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

Quick question, does the temp table have any employee records that are not yet in the master table?? IOW, do your INSERTs before your UPDATEs

>
> 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
Are you sure that is your corrected statement? Seems like it should be:
WHERE a.emp_no in (select emp_no from employee_tmp);

Another indication that there are IDs in employee_tmp that are not yet in the master employee table.

(I sure hope this is a school exercise.)

Ed Received on Tue Apr 09 2002 - 12:14:59 CDT