How to bulk update MySQL data with a single query?

MySQLMySQLi Database

You can bulk update MySQL data with one query using CASE command. The syntax is as follows −

update yourTableName
set yourUpdateColumnName = ( Case yourConditionColumnName WHEN Value1 THEN ‘’UpdatedValue’
WHEN Value2 THEN ‘UpdatedValue’
.
.
N
END)
where yourConditionColumnName IN(Value1,Value2,.....N);

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

mysql> create table UpdateAllDemo
   −> (
   −> BookId int,
   −> BookName varchar(200)
   −> );
Query OK, 0 rows affected (1.18 sec)

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

mysql> insert into UpdateAllDemo values(1000,'Introduction to C');
Query OK, 1 row affected (0.15 sec)
mysql> insert into UpdateAllDemo values(1001,'Introduction to Java');
Query OK, 1 row affected (0.21 sec)

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

mysql> select *from UpdateAllDemo;

The following is the output −

+--------+----------------------+
| BookId | BookName             |
+--------+----------------------+
| 1000   | Introduction to C    |
| 1001   | Introduction to Java |
+--------+----------------------+
2 rows in set (0.00 sec)

Now, we will do bulk update i.e. update the

  • Value ‘Introduction to C’ with the value 'C in Depth', and

  • Value ‘Introduction to Java’ with the value 'Java in Depth'.

You can use CASE command for this as shown above. The query is as follows −

mysql> update UpdateAllDemo
   −> set BookName = (CASE BookId WHEN 1000 THEN 'C in Depth'
   −> when 1001 THEN 'Java in Depth'
   −> END)
   −> Where BookId IN(1000,1001);
Query OK, 2 rows affected (0.24 sec)

Now you can check the values are updated in the table or not using select statement.

The query to check updated value in the table is as follows −

mysql> select *from UpdateAllDemo;

The following is the output displaying the bulk update is successful −

+--------+---------------+
| BookId | BookName      |
+--------+---------------+
| 1000   | C in Depth    |
| 1001   | Java in Depth |
+--------+---------------+
2 rows in set (0.00 sec)
raja
Published on 16-Jan-2019 12:24:09
Advertisements