UPDATE / Multiple SET not working - fields being set to NULL

UPDATE / Multiple SET not working - fields being set to NULL

Post by Greg Berthum » Thu, 15 Nov 2001 13:02:00



In the following, the fields are all being set to NULL.  The SQL was
constructed based on advice I got from this newsgroup.  What am I doing
wrong?  The EmployeeLookup table does contain employee id matches.

UPDATE PNR
SET FName = (SELECT RTRIM(EmployeeLookup.FirstName) + ' ' +
RTRIM(EmployeeLookup.MiddleInitial)
  FROM EmployeeLookup
  WHERE EmployeeLookup.EmployeeID = PNR.EmplNo),
    LName = (SELECT EmployeeLookup.LastName FROM EmployeeLookup
  WHERE EmployeeLookup.EmployeeID = PNR.EmplNo),
    User1 = (SELECT EmployeeLookup.Title FROM EmployeeLookup
  WHERE EmployeeLookup.EmployeeID = PNR.EmplNo),
    User2 = (SELECT EmployeeLookup.Building FROM EmployeeLookup
  WHERE EmployeeLookup.EmployeeID = PNR.EmplNo),
    Dept  = (SELECT EmployeeLookup.HeadCountDept FROM EmployeeLookup
  WHERE EmployeeLookup.EmployeeID = PNR.EmplNo),
    Div   = (SELECT EmployeeLookup.HeadCountDivision FROM EmployeeLookup
  WHERE EmployeeLookup.EmployeeID = PNR.EmplNo)

Thanks,
Greg Berthume

http://members.aol.com/gberthume

 
 
 

UPDATE / Multiple SET not working - fields being set to NULL

Post by Steve Kas » Thu, 15 Nov 2001 13:04:21


How about

update pnr
set fname = rtrim(employeelookup.firstname)+'
'+rtrim(employeelookup.middleinitial),
    lname = employeelookup.lastname,
....
from employeelookup, pnr
where employeelookup.employeeid = pnr.emplno

As you have it, there is no where clause on the update, so you are updating
every row of pnr, not just those that match an employee in the lookup
table.  When there is no match, all the selects will return null, and that
is what you'll update the column to.  By the way, if middleinitial can be
NULL, you will need ISNULL() at that point.

Steve Kass
Drew University


> In the following, the fields are all being set to NULL.  The SQL was
> constructed based on advice I got from this newsgroup.  What am I doing
> wrong?  The EmployeeLookup table does contain employee id matches.

> UPDATE PNR
> SET FName = (SELECT RTRIM(EmployeeLookup.FirstName) + ' ' +
> RTRIM(EmployeeLookup.MiddleInitial)
>   FROM EmployeeLookup
>   WHERE EmployeeLookup.EmployeeID = PNR.EmplNo),
>     LName = (SELECT EmployeeLookup.LastName FROM EmployeeLookup
>   WHERE EmployeeLookup.EmployeeID = PNR.EmplNo),
>     User1 = (SELECT EmployeeLookup.Title FROM EmployeeLookup
>   WHERE EmployeeLookup.EmployeeID = PNR.EmplNo),
>     User2 = (SELECT EmployeeLookup.Building FROM EmployeeLookup
>   WHERE EmployeeLookup.EmployeeID = PNR.EmplNo),
>     Dept  = (SELECT EmployeeLookup.HeadCountDept FROM EmployeeLookup
>   WHERE EmployeeLookup.EmployeeID = PNR.EmplNo),
>     Div   = (SELECT EmployeeLookup.HeadCountDivision FROM EmployeeLookup
>   WHERE EmployeeLookup.EmployeeID = PNR.EmplNo)

> Thanks,
> Greg Berthume

> http://members.aol.com/gberthume


 
 
 

UPDATE / Multiple SET not working - fields being set to NULL

Post by Greg Berthum » Thu, 15 Nov 2001 13:53:40


What you've suggested does not work.

Here's what I interpreted from your response but I know it's wrong.  I want
to fill some PNR values with values from the EmployeeLookup table.  The link
is the employee id.  How can I update multiple fields in a single SET?
Books Online is woefully inadequate in it's examples.





UPDATE PNR
SET FName = (RTRIM(EmployeeLookup.FirstName) + ' ' +
RTRIM(EmployeeLookup.MiddleInitial),
    LName = EmployeeLookup.LastName,
    User1 = EmployeeLookup.Title,
    User2 = EmployeeLookup.Building,
    Dept  = EmployeeLookup.HeadCountDept,
    Div   = EmployeeLookup.HeadCountDivision
FROM EmployeeLookup
WHERE EmployeeLookup.EmployeeID = PNR.EmplNo)

There is a syntax error.  Where's my formatting mistake?

Thanks,
Greg

 
 
 

UPDATE / Multiple SET not working - fields being set to NULL

Post by Steve Kas » Thu, 15 Nov 2001 14:07:45


It's the parentheses mostly.





UPDATE PNR
SET FName = RTRIM(EmployeeLookup.FirstName) + ' ' +
RTRIM(EmployeeLookup.MiddleInitial),
    LName = EmployeeLookup.LastName,
    User1 = EmployeeLookup.Title,
    User2 = EmployeeLookup.Building,
    Dept  = EmployeeLookup.HeadCountDept,
    Div   = EmployeeLookup.HeadCountDivision
FROM EmployeeLookup --I thought you needed , PNR here, but maybe not.
WHERE EmployeeLookup.EmployeeID = PNR.EmplNo


> What you've suggested does not work.

> Here's what I interpreted from your response but I know it's wrong.  I want
> to fill some PNR values with values from the EmployeeLookup table.  The link
> is the employee id.  How can I update multiple fields in a single SET?
> Books Online is woefully inadequate in it's examples.





> UPDATE PNR
> SET FName = (RTRIM(EmployeeLookup.FirstName) + ' ' +
> RTRIM(EmployeeLookup.MiddleInitial),
>     LName = EmployeeLookup.LastName,
>     User1 = EmployeeLookup.Title,
>     User2 = EmployeeLookup.Building,
>     Dept  = EmployeeLookup.HeadCountDept,
>     Div   = EmployeeLookup.HeadCountDivision
> FROM EmployeeLookup
> WHERE EmployeeLookup.EmployeeID = PNR.EmplNo)

> There is a syntax error.  Where's my formatting mistake?

> Thanks,
> Greg

 
 
 

1. Setting not Null field to ""(empty string) does not work with CRecordset

Hello,

I am trying set not Null field in MS SQL Server table to ""(empty string)
using CRecordset.  
It comes back saying that the field can not be Null.  
I have tried to use SetFieldNull( NULL, FALSE) after AddNew() so it would
set all fields to not NULL, it does not seem to help.
I am using VC4.2b and set CRecordset derived class property to "" before I
do Update().

Any help is appreciated,
Michael

2. Seeking Beta Testers for DBA TOOLS - Ztools (load,archive,etc.)

3. SET NULL / SET NOT NULL

4. Email From withing PL/SQL

5. How to do a Web-based demo?

6. Mandrake RPMs uploaded

7. SET NULL on NOT NULL field

8. Set a field as Null or Not Null

9. set default to not null setting?

10. error 17122 : initdata: Warning: Could not set working set size to 519104 KB.

11. Eventlog Mesg 17122 : initdata: Warning: Could not set working set size to 519104 KB