weird things happening in an update

From: Paul Fell <novaweb_at_iinet.net.au>
Date: 18 Apr 2002 23:20:08 -0700
Message-ID: <4b270a4e.0204182220.69c2ffcf@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 Received on Fri Apr 19 2002 - 01:20:08 CDT