Set a column on a populated table to not allow nulls where lots of rows already are set to null

Question

I've got a table with an active column of type bit that is mostly set to null. The active column was added as an after thought and mistakenly set to allow nulls. So SQL Server won't let me turn off the null flag because most of the fields are already set to null.

I tried setting a default value of 1 to the column and then removing the null flag but it wasn't having any of that. I think the default value only applies to inserts and not an alter.

I then tried to set all the null values to 1 explicitly with the following query:

Update cnc.aidPage set active = 1 where active = null

Unfortunately this doesn't affect any rows. I tried derivatives of this and found that it doesn't change anything only when the filter is trying for null. So the following work:

Update cnc.aidPage set active = 1 where active = 0
Update cnc.aidPage set active = null where active = 1

I would appreciate it if anyone could tell me why the filter doesn't work if I check for null or if there is an easier way to accomplish turning allow nulls off.

Answer 1

NULL isn't a value -- it's a placeholder to indicate the lack of any value, so you can't use math operators to check for it. You need to use:

UPDATE cnc.aidPage 
   SET active = 1 
 WHERE active IS NULL

Answer 2

Your problem is that "null" in most SQL implementations, unlike most imperative languages, doesn't "equal" anything. NULL doesn't even equal NULL explicitly. You have to use the special "is" operator to do null checks:

Update cnc.aidPage set active = 1 where active is null

This should work for you.

Answer 3

You can't use '=' to check for NULL. Instead use 'IS NULL':

Update cnc.aidPage 
set active = 1
where active IS NULL 

Answer 4

NULL is used as a placeholder for unknown or inapplicable values.

Try:

Update cnc.aidPage set active = 1 where active is null