Update a boolean to its opposite in SQL without using a SELECT

Question

Is it possible to upgrade a bool field by telling it to update the field to the opposite of what it is without having to select the value - check it then update accordingly which seems long winded...

A pseudo example of what i mean

UPDATE `table` SET `my_bool` = opposite_of(my_bool)

Currently i have to SELECT my_bool in one query then do a quick check on its value so i can update the table in a second query.

I was hopeing to cut that down to a single query if that is possible ?

score 64 · Accepted Answer · answered Mar 03 '13 at 06:17

64

use NOT

UPDATE `table` SET `my_bool` = NOT my_bool

answered Mar 03 '13 at 06:17

John Woo

258,903
69
498
492

1

So that makes true = false and false = true! Neat! :) thanks! – Sir Mar 03 '13 at 06:18
2

Got to wait 13 minutes to tick your answer - should be shorter if upvotes is high enough :P – Sir Mar 03 '13 at 06:18
is it `SET active = NOT active` or `SET active = NOT(active)` – Sir Mar 03 '13 at 06:27
if prefer `SET active = NOT active` – John Woo Mar 03 '13 at 06:29
ok it seems then my syntax error is related to having an INNER JOIN then – Sir Mar 03 '13 at 06:30
can you show me the whole query? one more thing, what is the datatype of `active`? – John Woo Mar 03 '13 at 06:31
this is mysql right? i see the backticks around columns. here's the correct query, `UPDATE users t1 INNER JOIN user_setings AS t2 ON t1.tid=t2.id SET active = NOT active WHERE t2.uid= ? AND t1.id=?` – John Woo Mar 03 '13 at 06:35
actually your syntax if for `sql server` :D – John Woo Mar 03 '13 at 06:38

score 28 · Answer 2 · edited May 22 '14 at 10:07

28

UPDATE `Table` SET `my_bool` = 1 ^ `my_bool`

edited May 22 '14 at 10:07

Stephen Ostermiller

23,933
14
88
109

answered May 22 '14 at 09:41

Ishan

403
4
9

2

This was the better option for me, for some reason I was getting errors when trying to use NOT in a INSERT INTO.....SELECT command, thanks – Starjumper Tech SL Jan 08 '20 at 10:51

score 1 · Answer 3 · answered Jan 10 '23 at 18:21

1

FOR Microsoft SQL Server Kindly use ~ sign.

For example:

UPDATE [AspNetUsers] SET IsDisabled = ~IsDisabled WHERE Id = '001893bd-4e4c-4ce1-9cd1-3b155a733774'

answered Jan 10 '23 at 18:21

Sajeeb Chandan Saha

705
10
12

Update a boolean to its opposite in SQL without using a SELECT

3 Answers3

Linked