Altering a column: null to not null

Mike_36307 0
Viewed 1521950

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.

alter-column alter-table null tsql sql-server
14 Answers

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
mdb_85620

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
  1. Alter the table to change the property of the column
ALTER TABLE MyTable
ALTER COLUMN MyNullableColumn MyNullableColumnDatatype NOT NULL
Ralph_Wiggum_71050

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

user_484780

this seems simpler, but only works on Oracle:

ALTER TABLE [Table] 
ALTER [Column] NUMBER DEFAULT 0 NOT NULL;

in addition, with this, you can also add columns, not just alter it. It updates to the default value (0) in this example, if the value was null.

user_4636860

Let's take an example:

TABLE NAME=EMPLOYEE

And I want to change the column EMPLOYEE_NAME to NOT NULL. This query can be used for the task:

ALTER TABLE EMPLOYEE MODIFY EMPLOYEE.EMPLOYEE_NAME datatype NOT NULL;
user_101850840

You can change the definition of existing DB column using following sql.

ALTER TABLE mytable modify mycolumn datatype NOT NULL;
user_56298300

  1. First make sure the column that your changing to not does not have null values select count(*) from table where column's_name is null

  2. Impute the missing values. you can replace the nulls with empty string or 0 or an average or median value or an interpolated value. It depends on your back fill strategy or forward fill strategy.

  3. Decide if the column values need to be unique or non-unique. if they need to be unique than add an unique constraint. Otherwise, see if performance is adequate or if you need to add an index.

user_40011770
Related