Odd behavior update
In the data warehouse stored procedures part, I have a procedure that compares the old project data with the new project data (the old data is in the table, the new one is in the temp table) and updates the old data.
The weird part is that if the old data is zero, then the update statement doesn't work. If I add the null operator the update works fine. My question is, why doesn't this work the way I thought it would?
One of several update statements:
update cube.Projects
set prServiceLine=a.ServiceLine
from @projects1 a
inner join cube.Projects
on a.WPROJ_ID=cube.Projects.pk_prID
where prServiceLine<>a.ServiceLine
a source to share
where prServiceLine<>a.ServiceLine
if prServiceLine is null or a.ServiceLine is null, then the result of this condition is null, not boolean
check it:
declare @x int, @y int
if @x<>@y print 'works'
if @x=@y print 'works!'
set @x=1
if @x<>@y print 'not yet'
if @x=@y print 'not yet!'
set @y=2
if @x<>@y print 'maybe'
if @x=@y print 'maybe!'
exit:
maybe
you will never see "work", "working!", "not yet" or "not yet!" get the exit. Only "Maybe" ("maybe!" If they were equal).
you cannot check for null values with! =, =, or <>, you need to use ISNULL (), COALESCE, NOT NULL or IS NULL in your WHERE if one of the values you are testing can be NULL.
a source to share
The Wikipedia article on SQL Nulls is actually really good - it explains how behavior is often not what you expect, returning "unknown" rather than true or false in many cases.
There are quite a few error problems when entering zeros ...
a source to share