Re: weird things happening in an update

novaweb_at_iinet.net.au (Paul Fell) wrote in message news:<4b270a4e.0204182220.69c2ffcf_at_posting.google.com>...
> Oracle 9i
>
> Still learning. I've read manuals, but still having problems.
>
> I have some code in a procedure which is meant to update the target
> table where there is a match with the source table. Here's the code :
>
> Update tblStaffDirectory
> set (Forenames,
> Preferred,
> Surname,
> Title,
> Business_Unit,
> Work_Email_Number,
> Private_Email_Number,
> Office_Telephone,
> Mobile_Telephone,
> Fax_Number,
> Company,
> Department,
> City,
> State,
> Postcode,
> Reporting_Employee) =
> (select a.Forenames,
> a.Preferred,
> a.Surname,
> a.Title,
> a.Business_Unit,
> a.Work_Email_Number,
> a.Private_Email_Number,
> a.Office_Telephone,
> a.Mobile_Telephone,
> a.Fax_Number,
> a.Company,
> a.Department,
> a.City,
> a.State,
> a.Postcode,
> a.Reporting_Employee
> from tbltmpStaffDirectory a
> where tblStaffDirectory.Employee_ID = a.Employee_ID);
>
> Employee_ID is the primary key in tblStaffDirectory.
>
> There are records in tblStaffDirectory which do not exist in
> tbltmpStaffDirectory. With the code, as I said earlier, all I want to
> do is to update tblStaffDirectory where the records do match (ie on
> Employee_ID). What is happening is all fields (Except Employee_ID
> naturally) whose records that do not exist in the tbltmpStaffDirectory
> are set to NULL (in addition to the update which should happen). Why?
> The Where clause should filter out non-matching records.
>
> Paul

The where clause is a where clause on the *subquery*, not on the *update* statement. You are missing a where clause on the update statement, to select the corresponding records only. You are now selecting all records and the behavior of this statement is as expected.
Add
where exists
(select 'x'
from tbltmpStaffDirectory a
 where tblStaffDirectory.Employee_ID = a.Employee_ID);

-- 
Hth

Sybrand Bakker
Senior Oracle DBA