Bulk change all entries for a particular field in MySQL?

MySQLMySQLi Database

Let us first create a demo table −

mysql> create table BulkChangeDemo
   -> (
   -> CustomerId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> CustomerName varchar(20),
   -> isEducated boolean
   -> );
Query OK, 0 rows affected (1.47 sec)

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

mysql> insert into BulkChangeDemo(CustomerName,isEducated) values('Larry',true);
Query OK, 1 row affected (0.09 sec)
mysql> insert into BulkChangeDemo(CustomerName,isEducated) values('John',false);
Query OK, 1 row affected (0.16 sec)
mysql> insert into BulkChangeDemo(CustomerName,isEducated) values('Carol',false);
Query OK, 1 row affected (0.25 sec)
mysql> insert into BulkChangeDemo(CustomerName,isEducated) values('Mike',true);
Query OK, 1 row affected (0.18 sec)
mysql> insert into BulkChangeDemo(CustomerName,isEducated) values('David',false);
Query OK, 1 row affected (0.13 sec)
mysql> insert into BulkChangeDemo(CustomerName,isEducated) values('Bob',false);
Query OK, 1 row affected (0.10 sec)

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

mysql> select *from BulkChangeDemo;

Here is the output −

+------------+--------------+------------+
| CustomerId | CustomerName | isEducated |
+------------+--------------+------------+
|          1 | Larry        | 1          |
|          2 | John         | 0          |
|          3 | Carol        | 0          |
|          4 | Mike         | 1          |
|          5 | David        | 0          |
|          6 | Bob          | 0          |
+------------+--------------+------------+
6 rows in set (0.00 sec)

The following is the query to bulk change all entries for a particular field −

mysql> update BulkChangeDemo set isEducated =true;
Query OK, 4 rows affected (0.18 sec)
Rows matched: 6 Changed: 4 Warnings: 0
raja
Published on 01-Apr-2019 15:10:34
Advertisements