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.
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