Menu
I have a field (say, foo) in a table in a SQL Server database that was originally defined as nullable, but new requirements indicate that this field must be non-null.
What's the best way of updating this field to non-null via an update script without deleting the contents of the table? I tried generating a script from the Design view, but fails during execution because the current contents of the table had NULL values for foo. Worse yet, if I ignored this error, it proceeds to delete all the contents of the table!
678
All you need to do is to replace [Table] with the name of your table, [Col] with the name of your column and TYPE with the datatype of the column. Execute the command and you are allowed to use NULL values for the specified column. That is all it takes to switch between NULL and NOT NULL .
To set a specific row on a specific column to null use: Update myTable set MyColumn = NULL where Field = Condition. This would set a specific cell to null as the inner question asks. Save this answer.
UPDATE [table] SET [column]=0 WHERE [column] IS NULL; Null Values can be replaced in SQL by using UPDATE, SET, and WHERE to search a column in a table for nulls and replace them. In the example above it replaces them with 0.
We can replace NULL values with a specific value using the SQL Server ISNULL Function. The syntax for the SQL ISNULL function is as follow. The SQL Server ISNULL function returns the replacement value if the first parameter expression evaluates to NULL.
You've got to set a value to any rows with NULL in the NULLable column before you change it to 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's what you want
ALTER TABLE t ADD CONSTRAINT t_foo_def DEFAULT 0 FOR foo
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
