sql-servert-sqlnullalter-tablealter-column

Altering a column: null to not null

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.

Solution

  • 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