Re: weird things happening in an update

From: Jonathan Bliss <bliss_jonathan_at_hotmail.com>
Date: 19 Apr 2002 04:20:33 -0700
Message-ID: <ae530df0.0204190320.3772d4c9@posting.google.com>

Paul

This is expected behaviour. An update is analagous to a select statement in that it will process every row in the table. Since you have no where clause on your update statement (outside the brackets) it is bound to process all records and where the embedded select statement returns no rows will update with null.

The answer is fairly simple, restrict the update clause, I would use the following because I find it easiest to work with - I think there are more elegant solutions.

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)

--
where exists (select 'x'
from tbltmpStaffDirectory b
where tblStaffDirectory.Employee_ID = b.Employee_ID);
--

Jon

novaweb_at_iinet.net.au (Paul Fell) wrote in message news:<4b270a4e.0204182220.69c2ffcf_at_posting.google.com>...





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

Received on Fri Apr 19 2002 - 06:20:33 CDT