CODEWITHSUNDEEP
sql-server
Rabskatran
Rabskatran

Reputation: 2299

How can I invert a nullable boolean using SQL?

I'm looking for a way to invert the value of a nullable boolean in MS-SQL. If the boolean field in NULL, I want to consider that the request acts as it was False.

My current request is 

UPDATE tableName SET booleanColumnName=(1 ^ booleanColumnName) WHERE xxx.

This don't work for NULL values as it remains NULL.

Upvotes: 0

Views: 1792

Answers (4)

ewahner
ewahner

Reputation: 1189

Or try

UPDATE tableName SET booleanColumnName = coalesce(1^booleanColumnName,0) WHERE xxx

Essentially the same as an IsNull(field, defaultValue) however, you could add more null checks like:

coalesce(booleanColumnName, someotherColumn, @default, 0)

Here is a test of my methodology

create table #test ( a bit, id int identity(1,1) )
insert into #test
select null
union
select 0
union
select 1

select *, coalesce(1^a, 0)
  from #test

You will see that it flips the bools and gives you a 0 for null.

Upvotes: 1

Philip Kelley
Philip Kelley

Reputation: 40319

@Giorgi's answer (+1) is the simplest (and first) solution for your code. An alternative that doesn't use wifty math tricks might be a bit clearer:

UPDATE tablename
 set booleanColumnName = case isnull(booleanColumnName, 0)
                           when 0 then 1
                           else 0
                         end

or even

UPDATE tablename
 set booleanColumnName = case booleanColumnName
                           when 1 then 0
                           else 1
                         end

Upvotes: 0

roman
roman

Reputation: 117400

What about

UPDATE tableName SET booleanColumnName=(1 ^ isnull(booleanColumnName, 0)) WHERE xxx.

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Use:

ISNULL(booleanColumnName, 0)

I.e.:

UPDATE tableName SET booleanColumnName = (1 ^ ISNULL(booleanColumnName, 0)) WHERE xxx.

Upvotes: 3

Related Questions