Altering a column: null to not null

Karmic Coder picture Karmic Coder · Mar 27, 2009 · Viewed 1.4M times · Source

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

mdb picture mdb · Mar 27, 2009

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