Altering a column: null to not null
First, make all current NULL values disappear:
UPDATE [Table] SET [Column]=0 WHERE [Column] IS NULLThen, update the table definition to disallow "NULLs":
ALTER TABLE [Table] ALTER COLUMN [Column] INTEGER NOT NULL
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];
You will have to do it in two steps:
- Update the table so that there are no nulls in the column.
UPDATE MyTable SET MyNullableColumn = 0WHERE MyNullableColumn IS NULL- Alter the table to change the property of the column
ALTER TABLE MyTableALTER COLUMN MyNullableColumn MyNullableColumnDatatype NOT NULL