How to set bool value in SQL

Question

I have a column which is bool. How can I set true, false values for that? Here is my query :

Update [mydb].[dbo].[myTable]
SET isTrue =
(
CASE WHEN Name = 'Jason' THEN 1
END
)

I don't know what to write after THEN keyword. Should I write 1 or true or 1 AS BIT or something else?

the query is correct, CASE WHEN Name = 'Jason' THEN 1 ELSE 0 END — Abdul Rasheed, Jan 17 '17 at 10:48
Possible duplicate of [Is there a Boolean data type in Microsoft SQL Server like there is in MySQL?](http://stackoverflow.com/questions/3138029/is-there-a-boolean-data-type-in-microsoft-sql-server-like-there-is-in-mysql) — Disillusioned, Jan 17 '17 at 10:48
Use `bit` type 1 = true and 0 = false. You won't need to cast, but currently you'd be leaving "false results" as `NULL`. So add `ELSE 0` to your `CASE` statement. — Disillusioned, Jan 17 '17 at 10:51

Zohar Peled · Accepted Answer · 2020-09-25T12:43:49.430

35

Sql server does not expose a boolean data type which can be used in queries.
Instead, it has a bit data type where the possible values are 0 or 1.
So to answer your question, you should use 1 to indicate a true value, 0 to indicate a false value, or null to indicate an unknown value.

Update [mydb].[dbo].[myTable]
SET isTrue =
CASE WHEN Name = 'Jason' THEN 
    1
ELSE 
    0
END

edited Sep 25 '20 at 12:43

answered Jan 17 '17 at 10:52

Zohar Peled

79,642
10
69
121

score 1 · Answer 2 · answered Jan 17 '17 at 10:52

The query you added will work fine, but it will you have to take care of "FALSE" part as well otherwise it will try to enter NULL in your column. Do you have any default value constrain on isTrue column?

Update [mydb].[dbo].[myTable]
SET isTrue =
(
   CASE WHEN Name = 'Jason' THEN 1 ELSE 0
END
)

score 1 · Answer 3 · answered Jan 17 '17 at 10:54

1

You need case statement with when and else if not any condition satisfied

Update [mydb].[dbo].[myTable]
SET isTrue = ( CASE WHEN Name = 'Jason' 
                   THEN 1 else 0 
               END)

answered Jan 17 '17 at 10:54

Vikram Jain

5,498
1
19
31

score 1 · Answer 4 · answered Nov 05 '19 at 12:47

Use IIF function of sql server

DECLARE @a int = 45, @b int = 40;  
DECLARE @a int = 45, @b int = 40;  
SELECT IIF ( @a > @b, 'TRUE', 'FALSE' ) AS Result; 

Result  
--------  
TRUE  

(1 row(s) affected)

For Your Problem

Update [mydb].[dbo].[myTable]
SET isTrue = ( Name = 'Jason', 'TRUE', 'FALSE' )

How to set bool value in SQL

4 Answers4

Linked

Related