Link to home
Start Free TrialLog in
Avatar of David Megnin
David MegninFlag for United States of America

asked on

Why does my WHERE clause not work in MS T-SQL?

The intent was for this to update only the fields that are NULL, but it updates them regardless of their value or lack thereof.  I did not want to change a value if it already existed; only if the field was NULL.
It's not a big deal, this will alway keep the field "correct" but I was curious why it didn't work as intended.  I may need the intended behavior at some time.
UPDATE    JobTitles
SET       Program = Employers.Program
FROM      JobTitles INNER JOIN Employers
ON        JobTitles.keyEmployerID = Employers.keyEmployerID
WHERE	 JobTitles.Program IS NULL

Open in new window

Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

UPDATE    JobTitles
SET       Program = Employers.Program
FROM      JobTitles INNER JOIN Employers
ON        JobTitles.keyEmployerID = Employers.keyEmployerID
WHERE       Isnull(JobTitles.Program,'') = ''


I suspect there are not null, but blank?
ASKER CERTIFIED SOLUTION
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of David Megnin

ASKER

>> I suspect there are not null, but blank?
In which case it should be ignored like any other value other than NULL.

>>The second time it should return 0, for the same condition.
Yes.  Right or wrong, NULL or not the update has already been done.

Hmmm...
UPDATE    JobTitles
SET      JobTitles.Program = Employers.Program
FROM      JobTitles INNER JOIN Employers
ON        JobTitles.keyEmployerID = Employers.keyEmployerID
WHERE       JobTitles.Program IS NULL

That did work.  The only difference is the table names are specified.  That's the strangest thing because in my real database the column in the Employers table does NOT have the same name.  I only made them the same in the example for clarity.  My actual queries are:

Cr@p!  I must be going out of my mind faster than expected.  Both querys work as expected now.  I don't know what was going on yesterday.  I tested it several times and it always updated the value even if it was already set to something else.  This morning it is leaving it alone if it already has a value.  
Thanks for your help.