How to compare null values ​​from a database column

Question

I have an entry in my table where the Name column is Null .. and I want to update this entry using the following query .. My sql query:

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 Nullwith the column value

How can I do that?

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

You can also use the 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 that cannot exist in the EMP table;

Answer 3

Tests with null values ​​are always false in SQL except IS NULL or IS NOT NULL. You must add the IS NULL clause to your WHERE:

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

Answer 4

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

Answer 5

According to my SQL knowledge, u can store in a variable, but cannot use it for comparison NULL

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