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)

Arjun Thakur

Published on 01-Feb-2019 11:47:22

• Related Questions & Answers
• How can we enter BOOLEAN values in MySQL statement?
• How can I add a Boolean field to MySQL?
• How can we update any value in MySQL view as we can update the values in MySQL table?
• How can I create a stored procedure to update values in a MySQL table?
• How can we update values in a MySQL table?
• How do I update NULL values in a field in MySQL?
• How can I update MySQL table after quoting the values of a column with a single quote?
• How can we update the values in one MySQL table by using the values of another MySQL table?
• How can I update child objects in MongoDB?
• How can we update columns values on multiple rows with a single MySQL UPDATE statement?
• How can I convert a string to boolean in JavaScript?
• How can you update certain values in a table in MySQL using Python?
• How can I update child objects in MongoDB database?
• How can I get enum possible values in a MySQL database?
• In MySQL, without having BOOLEAN data type how can we show TRUE and FALSE values?