Altering a column: null to not null

Question

I have a table that has several nullable integer columns. This is undesirable for several reasons, so I am looking to update all nulls to 0 and then set these columns to "NOT NULL." Aside from changing nulls to 0, data must be preserved.

I am looking for the specific sql syntax to alter a column (call it ColumnA) to "not null." Assume the data has been updated to not contain nulls.

Using sql server 2000.

Answer 1

As long as the column is not a unique identifier

UPDATE table set columnName = 0 where columnName is null

Then

Alter the table and set the field to non null and specify a default value of 0

Answer 2

just do an update set the field to 0 where field is null, then do the alter table.

Answer 3

First, make all current NULL values disappear:

UPDATE [Table] SET [Column]=0 WHERE [Column] IS NULL

Then, update the table definition to disallow NULLs:

ALTER TABLE [Table] ALTER COLUMN [Column] INTEGER NOT NULL

Answer 4

Let me value-add to the above two correct responses.
The semantics of NULL can have a few meanings. One of the common meaning is UNKNOWN. Take SCORE as an example. A null SCORE and a ZERO SCORE is a world of difference!

Before you did the above recommendations. Make sure you application does not take null in its business logic.

Answer 5

You will have to do it in two steps:

1. Update the table so that there are no nulls in the column. UPDATE MyTable SET MyNullableColumn = 0 WHERE MyNullableColumn IS NULL

2. Alter the table to change the property of the column ALTER TABLE MyTable ALTER COLUMN MyNullableColumn MyNullableColumnDatatype NOT NULL