How can I update the boolean values in MySQL?

You can update boolean value using UPDATE command. If you use the BOOLEAN data type, MySQL internally convert it into tinyint(1). It can takes true or false literal in which true indicates 1 to tinyint(1) and false indicates 0 to tinyint(1).

The syntax is as follows −

UPDATE yourTableName SET yourColumnName = yourValue WHERE yourCondition;

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table UpdateBooleans    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> isSuccessful BOOLEAN,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (1.55 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into UpdateBooleans(isSuccessful) values(true); Query OK, 1 row affected (0.17 sec) mysql> insert into UpdateBooleans(isSuccessful) values(false); Query OK, 1 row affected (0.21 sec) mysql> insert into UpdateBooleans(isSuccessful) values(true); Query OK, 1 row affected (0.13 sec) mysql> insert into UpdateBooleans(isSuccessful) values(false); Query OK, 1 row affected (0.15 sec) mysql> insert into UpdateBooleans(isSuccessful) values(false); Query OK, 1 row affected (0.24 sec) mysql> insert into UpdateBooleans(isSuccessful) values(false); Query OK, 1 row affected (0.17 sec) mysql> insert into UpdateBooleans(isSuccessful) values(true); Query OK, 1 row affected (0.12 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from UpdateBooleans;

The following is the output −

+----+--------------+ | Id | isSuccessful | +----+--------------+ | 1  | 1            | | 2  | 0            | | 3  | 1            | | 4  | 0            | | 5  | 0            | | 6  | 0            | | 7  | 1            | +----+--------------+ 7 rows in set (0.00 sec)

Here is the query to update boolean values. Let us update all 0s to 1:

mysql> update UpdateBooleans set isSuccessful = true where isSuccessful = false; Query OK, 4 rows affected (0.15 sec) Rows matched: 4 Changed: 4 Warnings: 0

Display the records from the table once again. The query is as follows:

mysql> select *from UpdateBooleans;

The following is the output:

+----+--------------+ | Id | isSuccessful | +----+--------------+ | 1  | 1            | | 2  | 1            | | 3  | 1            | | 4  | 1            | | 5  | 1            |             | 6  | 1            | | 7  | 1            | +----+--------------+ 7 rows in set (0.00 sec)