David Megnin
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.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
Thanks for your help.
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?