MYSQL toggle enum values on each call

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.

Answer

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 |
+-------+