Re: Intermediate:Update SQL question

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

This is starting to get too deep into your business processes, but why would you have to update the manager? (maybe you mean insert?)

>
> >
> > >
> > > 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.
Do you then delete these rows from the temp table?

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

same session and commit is not the issue. That you asked this indicates you are not really sure what you are doing.
>
> >
> >
> > (I sure hope this is a school exercise.)
> >
> > Ed

Bottom line is when you said:
> > > 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)
> > > );

you are really saying update ALL rows in the employee table with  the values in the temp table. If there are employees in the master  table which are NOT in the temp table, you are trying to set their  values to NULL. That's why you have to have that where clause.

Please go back and read your references about relational databases. Then read you ORACLE manuals to understand the UPDATE command.

(PLEASE say this was a school assignment, or that your boss has you taking some database training. I'd be concerned with letting you loose on a production system, especially an HR system. Thank goodness for constraints! I'm not trying to insult you, but your skill level doesn't seem ready for real work yet.) Received on Wed Apr 10 2002 - 08:35:29 CDT