CODEWITHSUNDEEP
mysql
Monty
Monty

Reputation: 1322

MYSQL Using UPDATE, WHEN THEN with a NULL value

I'm trying to figure out why I'm not getting a result from a MySQL Query I'm running.

I'm trying to replace a NULL value with a number with in a query, but I can't figure out what I'm doing wrong.

Here's my query:

UPDATE Details
SET HowHear_ID = CASE HowHear_ID
  WHEN '' THEN 25   
  WHEN NULL THEN 25                         
  WHEN 7  THEN 25
  WHEN 8  THEN 5
  WHEN 16 THEN 25
  WHEN 17 THEN 16
END
WHERE HowHear_ID IN ('',NULL,7,8,16,17)

This Query will effect all but the NULL values.

What am I doing wrong??

Upvotes: 1

Views: 104

Answers (3)

Lukas Eder
Lukas Eder

Reputation: 221350

No value will ever equal (or "unequal") NULL in SQL. Understand the following truth table:

NULL = NULL yields NULL   -- not FALSE!
NULL != NULL yields NULL  -- not TRUE!
[ANY] = NULL yields NULL  -- not FALSE!
[ANY] != NULL yields NULL -- not TRUE!

Since the following are equivalent...

[expression] IN (a, b, c)
[expression] = ANY (a, b, c)
[expression] = a OR [expression] = b OR [expression] = c

... you cannot put NULL on the right hand side of an IN predicate. Interestingly, things get even worse when you put NULL on the right hand side of a NOT IN predicate:

[expression] NOT IN (a, b, c)
[expression] != ANY (a, b, c)
[expression] != a AND [expression] != b AND [expression] != c

If b were NULL, the whole expression will become NULL (or maybe FALSE), but never TRUE. This is also the case for NOT IN (subselect) predicates! So, never do this:

[expression] NOT IN (NULL, 1, 2)

The correct solution in your case uses a NULL predicate instead. Do this:

UPDATE Details
SET HowHear_ID = CASE 
  WHEN HowHear_ID = ''    THEN 25   
  WHEN HowHear_ID IS NULL THEN 25  -- Use a NULL predicate here
  WHEN HowHear_ID = 7     THEN 25
  WHEN HowHear_ID = 8     THEN 5
  WHEN HowHear_ID = 16    THEN 25
  WHEN HowHear_ID = 17    THEN 16
END
WHERE HowHear_ID IN ('',7,8,16,17)
   OR HowHear_ID IS NULL           -- Use a NULL predicate here

Or this:

WHERE COALESCE(HowHear_ID, '') IN ('',7,8,16,17)

Upvotes: 4

codingbiz
codingbiz

Reputation: 26396

You can achieve that with 

WHERE HowHear_ID IN ('',7,8,16,17) OR HowHear_ID IS NULL

Upvotes: 1

Spudley
Spudley

Reputation: 168853

You can't reference NULL in a WHERE clause and get the results you expect. NULL behaves differently to other values.

If you need to reference it, you need to use the isnull() function.

in your case, you would write something like this:

WHERE HowHear_ID IN ('',7,8,16,17) or isnull(HowHear_ID)

By the way, you haven't specified the data type of the field. I assume it's an integer though. In that case, it might be better to check for zero rather than an empty string? (if it isn't an integer, then perhaps it should be)

Upvotes: 1

Related Questions