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 use MySQL CASE statement while using UPDATE Query?
For using MySQL CASE statement while using UPDATE Query, you can use CASE statement. Let us first create a table −
mysql> create table DemoTable ( UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, UserScore int ); Query OK, 0 rows affected (0.29 sec)
Insert some records in the table using insert command −
mysql> insert into DemoTable(UserScore) values(100); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable(UserScore) values(110); Query OK, 1 row affected (0.07 sec) mysql> insert into DemoTable(UserScore) values(120); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable(UserScore) values(200); Query OK, 1 row affected (0.05 sec) mysql> insert into DemoTable(UserScore) values(230); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(UserScore) values(250); Query OK, 1 row affected (0.03 sec) mysql> insert into DemoTable(UserScore) values(270); Query OK, 1 row affected (0.06 sec)
Display all records from the table using select statement −
mysql> select *from DemoTable;
This will produce the following output −
+--------+-----------+ | UserId | UserScore | +--------+-----------+ | 1 | 100 | | 2 | 110 | | 3 | 120 | | 4 | 200 | | 5 | 230 | | 6 | 250 | | 7 | 270 | +--------+-----------+ 7 rows in set (0.00 sec)
Following is the query to use CASE statement while using UPDATE query −
mysql> update DemoTable
set UserScore =
CASE
WHEN UserScore BETWEEN 100 AND 120
THEN UserScore + 5
WHEN UserScore BETWEEN 130 AND 230
THEN UserScore +10
WHEN UserScore >=250
THEN UserScore * 5
ELSE UserScore
END;
Query OK, 7 rows affected (0.06 sec)
Rows matched: 7 Changed: 7 Warnings: 0Now you can display all records from the table once again −
mysql> select *from DemoTable;
This will produce the following output −
+--------+-----------+ | UserId | UserScore | +--------+-----------+ | 1 | 105 | | 2 | 115 | | 3 | 125 | | 4 | 210 | | 5 | 240 | | 6 | 1250 | | 7 | 1350 | +--------+-----------+ 7 rows in set (0.00 sec)
Updated on: 30-Jul-2019
355 Views
Advertisements