Advertisement
Advertisement


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.

2015/12/15
1
1233
12/15/2015 7:23:04 AM

Accepted Answer

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
2020/04/19
2034
4/19/2020 11:23:32 AM

I had the same problem, but the field used to default to null, and now I want to default it to 0. That required adding one more line after mdb's solution:

ALTER TABLE [Table] ADD CONSTRAINT [Constraint] DEFAULT 0 FOR [Column];
2016/06/16

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
2018/04/10

For Oracle 11g, I was able to change the column attribute as follows:

ALTER TABLE tablename MODIFY columnname datatype NOT NULL;

Otherwise abatichev's answer seemed good. You can't repeat the alter - it complains (at least in SQL Developer) that the column is already not null.

2012/09/17

this worked for me:

ALTER TABLE [Table] 
Alter COLUMN [Column] VARCHAR(50) not null;
2012/06/05

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

2009/03/27

Source: https://stackoverflow.com/questions/689746
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]