Simple way to toggle a value of an int field in MySQL

To toggle a value of an int field, you can use update command with if(). The syntax is as follows −

update yourTableName set yourColumnName = IF(yourColumnName = 0, 1, 0);

To understand the above toggle syntax, create a table with some int value. The query to create a table is as follows −

mysql> create table ToggleDemo
   −> (
      −> IsOnOrOff int
   −> );
Query OK, 0 rows affected (0.53 sec)

Let us insert int values in the table with the help of insert command. The query is as follows −

mysql> insert into ToggleDemo values(1);
Query OK, 1 row affected (0.25 sec)

mysql> insert into ToggleDemo values(0);
Query OK, 1 row affected (0.15 sec)

mysql> insert into ToggleDemo values(1);
Query OK, 1 row affected (0.17 sec)

mysql> insert into ToggleDemo values(0);
Query OK, 1 row affected (0.11 sec)

mysql> insert into ToggleDemo values(1);
Query OK, 1 row affected (0.10 sec)

mysql> insert into ToggleDemo values(0);
Query OK, 1 row affected (0.09 sec)

Displaying all records inserted above −

mysql> select *from ToggleDemo;

The following is the output −

+-----------+
| IsOnOrOff |
+-----------+
|         1 |
|         0 |
|         1 |
|         0 |
|         1 |
|         0 |
+-----------+
6 rows in set (0.00 sec)

Now you can implement the syntax we discussed in the beginning to toggle an int value. The query is as follows −

mysql> update ToggleDemo set IsOnOrOff = IF(IsOnOrOff = 0, 1, 0);
Query OK, 6 rows affected (0.12 sec)
Rows matched: 6 Changed: 6 Warnings − 0

Now you can check whether the column value is toggled or not. The query is as follows −

mysql> select *from ToggleDemo;

The following is the output displaying toggle is successful −

+-----------+
| IsOnOrOff |
+-----------+
|         0 |
|         1 |
|         0 |
|         1 |
|         0 |
|       1 |
+-----------+
6 rows in set (0.00 sec)