Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to bulk update MySQL data with a single query?
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)
5K+ Views
