Cannot insert the value NULL into column (UPDATE with CASE, SQL Server)

Question

I would like to update a table using a CASE expression, but I get an error, however I do not SET NULL values:

Cannot insert the value NULL into column 'Column2', table 'Table1'; column does not allow nulls. UPDATE fails.

My code:

UPDATE [Table 1]
SET [Column1] = '2', 
    [Column2] = CASE 
                   WHEN [Column3] = 'A' THEN '1'
                   WHEN [Column3] = 'B' THEN '2'
                   WHEN [Column3] = 'C' THEN '3'
                END
WHERE [Column4] IN ('1118')

Any suggestions what I'm doing wrong?

When Column3 not equals 'A,B,C', what must be inserted to column2? — GGadde, Dec 04 '18 at 16:21

score 2 · Answer 1 · edited Dec 04 '18 at 16:24

The value in [Column3] isn't in the list of values specified in your CASE statement (i.e. it's not A, B or C), so the result of the statement is NULL. CASE implicitly returns ELSE NULL when not specified otherwise.

From the DOCS

ELSE else_result_expression Is the expression returned if no comparison operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL. else_result_expression is any valid expression. The data types of else_result_expression and any result_expression must be the same or must be an implicit conversion.

To handle this possibility, add an ELSE clause that returns either the existing value (shown below) or a default value of your choice. For example:

UPDATE [Table 1]
SET [Column1] = '2', [Column2] = 

CASE 
    WHEN [Column3] = 'A' THEN '1'
    WHEN [Column3] = 'B' THEN '2'
    WHEN [Column3] = 'C' THEN '3'
    ELSE [Column2]
END

WHERE [Column4] IN ('1118')

score 1 · Answer 2 · answered Dec 04 '18 at 16:19

You need to add and 'ELSE' clause to your CASE Statement, you can make this a column value or a specific value of your choice

UPDATE [Table 1]
SET [Column1] = '2', [Column2] = 

CASE 
    WHEN [Column3] = 'A' THEN '1'
    WHEN [Column3] = 'B' THEN '2'
    WHEN [Column3] = 'C' THEN '3'
    ELSE [Column2]
END

WHERE [Column4] IN ('1118')

OR

UPDATE [Table 1]
SET [Column1] = '2', [Column2] = 

CASE 
    WHEN [Column3] = 'A' THEN '1'
    WHEN [Column3] = 'B' THEN '2'
    WHEN [Column3] = 'C' THEN '3'
    ELSE '000' --your choice
END

WHERE [Column4] IN ('1118')

Daniel N · Answer 3 · 2018-12-04T16:28:29.483

0

use a select statement to check the values that you would be setting, that should give you an answer as to what is going wrong here.

Select [Column1] = '2', [Column2] = 
CASE 
    WHEN [Column3] = 'A' THEN '1'
    WHEN [Column3] = 'B' THEN '2'
    WHEN [Column3] = 'C' THEN '3'
END
,[Column3] --possibly has a value that is not covered by case stmt
FROM [Table 1]
WHERE [Column4] IN ('1118')

edited Dec 04 '18 at 16:28

answered Dec 04 '18 at 16:17

Daniel N

1,122
1
8
14

you are right, I should have also included column3 in the output, then it would be even easier to troubleshoot why column2 is being set to null.. updated – Daniel N Dec 04 '18 at 16:27
in this case, you'd want to add the predicate `where [Column3] not in ('A','B','C')` to limit the rows to those which don't fit the `case` – S3S Dec 04 '18 at 16:31
maybe, but given that there are only three values in the example case stmt specified and based on some id value, my guess is that column3 has two additional possible values, blanks and nulls and my answer would be sufficient to point in the right direction. Of course it's anyone's guess as to how many possible values and rows would be touched by this update stmt in the real world, so in some cases not in query may not be the best choice either :) – Daniel N Dec 04 '18 at 16:43
Sure it could have two, or two hundred but my point is why return the rows that *did* fit the `case` when you want to determine which ones *didn't*? Just seems to clutter up the result set is all, which is why i suggested the `where` clause – S3S Dec 04 '18 at 16:49
my preference is to see the whole picture. let's say the field is varchar(2) and values are 'Y', ' Y', 'N', it would be hard to see that leading space in front of the value if it's excluded from result. – Daniel N Dec 04 '18 at 16:57

score 0 · Answer 4 · answered Dec 04 '18 at 16:18

Based on the error , column 2 in table [Table 1] is "non nullable" and that's why you are getting the error. There can be 2 solutions - 1. Either you change table definition to allow null or update your case statement that whether it is fulfilling conditions. It will also be useful if you can post the data which is there in the table where column4 is 1118.

Cannot insert the value NULL into column (UPDATE with CASE, SQL Server)

4 Answers4