How to change all bit type column NULL values to false in all database in MSSQL?

sql-server null multiple-columns false-positive
Sergey Khojoyan picture Sergey Khojoyan · Sep 6, 2012 · Viewed 30.6k times · Source

I have a database in with I have many bit type columns. After adding other columns I need all old columns to have default "false" values.

Answer

LittleBobbyTables - Au Revoir picture LittleBobbyTables - Au Revoir · Sep 6, 2012

To update each old column to 0, you can use this query (on a column-by-column basis):

UPDATE MyTable
SET OldCol1 = 0
WHERE OldCol1 IS NULL

Then, if you want any future values to have a default of 0 as well, use this:

ALTER TABLE MyTable
ALTER COLUMN OldCol1 bit NOT NULL

ALTER TABLE MyTable
ADD CONSTRAINT OldCol1ShouldBeFalse DEFAULT 0 FOR OldCol1

Now, you'll have to run this against each old column, so hopefully you don't have too many.

Related questions

Altering a column: null to not null

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 …

Read More
How to use NULL or empty string in SQL

I would like to know how to use NULL and an empty string at the same time in a WHERE clause in SQL Server. I need to find records that have either null values or an empty string. Thanks.

Read More
Not equal <> != operator on NULL

Could someone please explain the following behavior in SQL? SELECT * FROM MyTable WHERE MyColumn != NULL (0 Results) SELECT * FROM MyTable WHERE MyColumn <> NULL (0 Results) SELECT * FROM MyTable WHERE MyColumn IS NOT NULL (568 Results)

Read More