1

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??

Improve this question

3 Answers 3

Reset to default
4

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Good explanation. However, I still have the NULL values after the Query. It does not replace the NULL values with the 25 that I'am trying to achieve.
@Monty: I see, well you don't need that CASE expression, since you're updating everything to 25. See the updated answer
Sorry, I left part of the code out as I have over 900 lines for it. They update to different number not only 25. I'll edit that.
@Monty: OK, I fixed my UPDATE statement example, to match yours. The same NULL-related rules apply to the CASE expression
@Monty: No worries. SQL NULLs keep surprising me time and again, myself :-) For completeness, I'll update what happens when you put NULL in a NOT IN predicate!
1

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)

Improve this answer

2 Comments

This runs, but does not replace the NULL values with the 25. What am I missing?
you need to do the same for the when clause too. Change the null to isnull()
1

You can achieve that with 

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.