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

LVL 1
megninAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cyberkiwiCommented:
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?
cyberkiwiCommented:
In fact, using your original query, to check, run these

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

select @@rowcount

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

select @@rowcount


The second time it should return 0, for the same condition.  Okay maybe if Employers.Program is also null then the 2nd count will include it.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
scorneoCommented:
check this out. Is this causing problem ?

UPDATE    JobTitles
SET      JobTitles.Program = Employers.Program
FROM      JobTitles INNER JOIN Employers
ON        JobTitles.keyEmployerID = Employers.keyEmployerID
WHERE       JobTitles.Program IS NULL
megninAuthor Commented:
>> 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.  
megninAuthor Commented:
Thanks for your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.