Re: Intermediate:Update SQL question

Ed prochak wrote:

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

A employee can be moved to a different manager. That's why when a new file is loaded into the temp table I update the manager also.

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

Ya, I do, the 1st SQL is Insert into employee where emp_no are not in the tmp table. The update is the next SQL.
>
> >
> > 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);
>

Ya, u right. sorry about the typo.

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

I insert all the new records. Do I have to commit the insert before I execute the update. ? Both the SQL's are fired in the same session.

>
>
> (I sure hope this is a school exercise.)
>
> Ed

--


Regards,

Praveen Mohan Mohanan