Selecting rows based on a SQL Server "bit" column

Question

In a SQL Server table, I have a BIT column and based its value, I need to update that table's other columns with some values. I tried this

UPDATE tablename SET Completed = GETDATE() WHERE CheckTaskAvailable = TRUE

but I get the error

Msg 207, Level 16, State 1, Server SQLDEV01, Line 1
Invalid column name 'TRUE'.

How to do this in a T-SQL query?

@AmeyaDeshpande In that table I have columns like Task Name, Completed, TaskAdded Date, CheckTaskAvailable. 'CheckTaskAvailable' is have bit as data type, based on this I need update 'Completed' column as 0 or 1. — Tarun, Apr 08 '15 at 11:17

score 4 · Answer 1 · answered Apr 08 '15 at 11:08

4

if you want to set as true try

Update table set columnName = 1 where ...

if you want to set as false try

Update table set columnName = 0 where ...

answered Apr 08 '15 at 11:08

Reza

18,865
13
88
163

score 2 · Answer 2 · answered Mar 03 '17 at 13:59

In addition to using the values 0 and 1, the T-SQL documentation for bit says that

The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.

so these work, too:

UPDATE tablename SET bitcolumn = 'TRUE' WHERE ...

UPDATE tablename SET othercolumn = 'something' WHERE bitcolumn = 'TRUE'

score 0 · Answer 3 · answered Feb 16 '17 at 15:08

I had a need to do something similar where I was looking to update a field based on whether a record existed or not existed in another table so I used above code (thank you RezaRahmati) and added:

Update table set columnName = 1 where ID IN (SELECT ID FROM other table)

or for false

Update table set columnName = 0 where ID NOT IN (SELECT ID FROM other table)

I really enjoy Stack Overflow it has really helped me learn.

Selecting rows based on a SQL Server "bit" column

3 Answers3