MYSQL toggle enum values on each call

Question

I want this to be like this.

I have an ENUM column with 0 AND 1, i want to toggle between these two values on each query.

UPDATE settings
SET state = 1 IF state = 0 
              ELSE IF state = 0 
                   SET STATE = 1 WHERE id = '$id';

I tried this, but it leaves the column empty.

UPDATE settings SET state = IF(state=1, 0, 1)

Thanks.

Why not SET state = (1 - state) WHERE... If the column is initially NULL, you have to set default value = 0 or use state= 1- IFNULL(state,0) — i486
– i486, Commented Nov 10, 2014 at 14:00
Ok, then "state = 1 - state" will work. When 1 it will be set to 0, and when 0 - to 1. — i486
– i486, Commented Nov 10, 2014 at 14:16
@i486 maybe there's something wrong with my system, this acts like the answer below. Sets state = '', and them '0', then ''; not 1. Anyway, can you put this as the answer and explain the maths? — Relm
– Relm, Commented Nov 10, 2014 at 14:24
Define column as INT or TINYINT (or even BIT). With enum it looks like string/varchar, i.e. no math. — i486
– i486, Commented Nov 10, 2014 at 14:38

fancyPants · Accepted Answer · 2014-11-10 15:04:30Z

2

I don't like enum for various reasons. Especially when you use it to store 0 and 1 it can be confusing, yes, even error prone.

Use a tinyint, it's much easier to use and more readable.

mysql> create table switch(state tinyint default 0);

mysql> insert into switch values (0);

mysql> select * from switch;
+-------+
| state |
+-------+
|     0 |
+-------+

mysql> update switch set state = not state; /*simple as that :)*/

mysql> select * from switch;
+-------+
| state |
+-------+
|     1 |
+-------+

mysql> update switch set state = not state;

mysql> select * from switch;
+-------+
| state |
+-------+
|     0 |
+-------+

edited Nov 10, 2014 at 15:04

answered Nov 10, 2014 at 14:44

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

fancyPants Over a year ago

1 and 0 are treated as true and false.

Nehal Shrivashtava · Accepted Answer · 2021-06-06 08:40:12Z

0

You can also use simple if statement :

mysql > UPDATE switch SET state=if(state=0,1,0);

answered Jun 6, 2021 at 8:40

Nehal Shrivashtava

614 bronze badges

Collectives™ on Stack Overflow

MYSQL toggle enum values on each call

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related