Change column to not allow null values

Question

So, I want to change the column in my SQL Server database to not allow null, but I keep getting the error. this is the sql statement i am using:

alter table [dbo].[mydatabase] alter column WeekInt int not null 

and this is the error I get:

 Msg 515, Level 16, State 2, Line 1 Cannot insert the value NULL into column 'WeekInt', table 'CustomerRadar.dbo.tblRWCampaignMessages'; column does not allow nulls. UPDATE fails. The statement has been terminated. 

I am sure my sql is correct and there are no zeros in the column I'm trying to change, so I'm really not sure what causes the problem. Any ideas? I'm at a dead end.

Answer 1

Obviously, the table has NULL values. What you can check:

 select * from mydatabase where WeekInt is NULL; 

Then you can do one of two things. Or change the values:

 update mydatabase set WeekInt = -1 where WeekInt is null; 

Or remove offensive lines:

 delete from mydatabase where WeekInt is null; 

Then, when all the values ​​are in order, you can make an alter table statement.

Answer 2

If you are trying to change the column so that it is not null and you get this error message, but it seems that the column has no is null values, make sure you check is null and not = null (which gives different results).

 Select * from ... where column is null 

instead

 Select * from ... where column = null 

I add this because it baffled me and took some time to resolve.