Odd Update Behavior

Question

In a part of my data warehousing stored procedures, i have a procedure that compares old project data to new project data (old data is in a table, new in temp table) and updates the old data.

The weird part is that if the old data is null, then the update statement doesn't work. If i add a is null statement the update works fine. My question is, why doesn't this work like i thought it would?

One of the multiple 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

Answer 1

Perhaps you need a LEFT JOIN (or RIGHT depending on data) rather than an INNER JOIN.

Answer 2

where prServiceLine<>a.ServiceLine

if prServiceLine is null or a.ServiceLine is null then the result of the condition is null not a boolean

test this:

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!'

output:

maybe

you will never see the 'works', 'works!', 'not yet', or 'not yet!' get output. Only the Maybe ('maybe!', if they were both equal).

you can't test null values with !=, =, or <>, you need to use ISNULL(), COALESCE, IS NOT NULL, or IS NULL in your WHERE if one of the values you are testing can be NULL.

Answer 3

The Wikipedia article about SQL Nulls is actually really good - it explains how the behaviour is frequently not what you would expect, returning "unknown" rather than true or false in many instances.

There are quite a few gotchas involved if you introduce nulls...