How to compare Null values from the database colum

Question

This question already has an answer here:

• Avoiding IF ELSE due to variable been NULL 3 answers

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

Try this UPDATE emp SET name = "gaurav" WHERE (@name IS NULL AND name IS NULL)

Answer 3

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 4

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)**

Answer 5

As per my SQL Knowledge u can store NULL in a variable, but can't use it for comparing

update emp set name="gaurav" where name is Null