Re: weird things happening in an update

You didn't limit the selection of rows in tblStaffDirectory to which to apply the update, so your update processes ALL rows. For those rows that have corresponding row in tbltmpStaffDirectory, update works correctly, for others it sets all updated columns to NULL because nothing was selected from tbltmpStaffDirectory.
> The Where clause should filter out non-matching records.
This would be true if your WHERE clause was applied for the update itself. It is only applied to subquery which selects new values for updated row. Fix is obvious: add the following as the last line of your update:

WHERE Employee_ID IN (select Employee_ID from tbltmpStaffDirectory)

This will limit rows selected for update to only those that have matching row in temporary table.

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Paul Fell" <novaweb_at_iinet.net.au> 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