Update script to update table field in SQL Server from NULL to non-NULL

Question

I have a field (say foo) in a table in a SQL Server database that was initially defined as NULL, but new requirements indicate that this field must not be null.

What is the best way to update this field to nonzero by updating the script without deleting the contents of the table? I tried to create a script from the Design view, but failed at runtime because the current contents of the table are NULL for foo. Even worse, if I ignored this error, it continues to delete the entire contents of the table!

Answer 1

NULL NULL, NOT NULL.

-- Clean up the data which won't comply with the schema changes
UPDATE t SET foo = 0 WHERE foo IS NULL

-- Apply the NOT NULL
ALTER TABLE t ALTER COLUMN foo int NOT NULL

-- Add a default for the future if that what you want
ALTER TABLE t ADD CONSTRAINT t_foo_def DEFAULT 0 FOR foo

Answer 2

, . nullable not-nullable, , .