How to compare Null values from the database column [duplicate]

Question

I have record in my table where Name column has Null value.. and I want to update that record using below query.. My sql query is:

set @Name=NUll;

update emp set name="gaurav" where name=@Name

When I run this query.. It will not update the record.. It does not compare the value Null to column value

How can this be done?

Answer 1

SET @Name = NULL;

UPDATE emp
SET name="gaurav"
WHERE    (@Name IS NULL     AND name IS NULL)
      OR (@Name IS NOT NULL AND name = @Name)

Answer 2

Also you can use following condition with ISNULL()

SET @Name = NULL;

UPDATE emp SET name='gaurav' WHERE ISNULL(@Name,'XXXXXXX')=ISNULL(Name,'XXXXXXX'); 

Where 'XXXXXXX' is a unique string constant which can't exist in EMP table;

Answer 3

Tests with null values are always false in SQL, except IS NULL or IS NOT NULL. you should add a IS NULL clause to your WHERE:

WHERE name = @name
**OR (name IS NULL and @name IS NULL)**