CODEWITHSUNDEEP
sql-servert-sql
user1260827
user1260827

Reputation: 1520

Set false in specific column to all rows

I have a table with the following columns:

ID    Name 
1     test1
2     test2

Now I added new column IsConfirmed. And this column contain null in all rows.

ID    Name   IsConfirmed
1     test1     null
2     test2     null

How can I set false to IsConfirmed column to all rows in the table using T-SQL?

Thanks

Upvotes: 6

Views: 18856

Answers (2)

JohnFx
JohnFx

Reputation: 34909

UPDATE YourTableName
SET IsConfirmed=0
WHERE isConfirmed is Null

Upvotes: 19

Andriy M
Andriy M

Reputation: 77687

@JohnFx's suggestion is a perfect solution to the problem in hand. However, you might want to be interested to know how to prevent this kind of situation from appearing and, at the same time, possibly to solve other potential issues.

In particular, the fact that you wanted to replace the NULLs with 0s immediately might indicate that you never wanted the column to hold NULLs in the first place. If that is actually the case, you should have added the column with the NOT NULL specifier:

ALTER TABLE tablename
ADD IsConfirmed bit NOT NULL

Of course, if you try to add a NOT NULL column to a non-empty table, you'll get an error if you do not also provide a default value for the column, because SQL Server would attempt to add the column with NULLs, which, according to the definition, are not allowed. So, add NOT NULL columns like this instead:

ALTER TABLE tablename
ADD IsConfirmed bit NOT NULL
CONSTRAINT DF_tablename_IsConfirmed DEFAULT (0)

The CONSTRAINT DF_tablename_IsConfirmed part is optional, you can leave just DEAFULT (0), but I would still recommend you to give explicit names to all your constraints, including the defaults, because that way it would be easier for you to drop/recreate them. And for the same reason (to make your managing the constraints easier), it would be better for you to use fixed patterns in naming the constraints. The pattern in the above statement (DF for DEFAULT, followed by the table name, followed by the column name) is just an example, you could very well come up a different one. But once you have, just be consistent.

Upvotes: 2

Related Questions