Syntax error Can we use WHERE clause inside MySQL CASE statement?

Can we use WHERE clause inside MySQL CASE statement?

For this, use the CASE WHEN statement. Let us first create a table −

mysql> create table DemoTable1040
(
   Value1 int,
   Value2 int,
   Value3 int
);
Query OK, 0 rows affected (0.65 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1040 values(10,30,1);
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable1040 values(40,20,50);
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable1040 values(80,90,100);
Query OK, 1 row affected (0.11 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable1040;

This will produce the following output −

+--------+--------+--------+
| Value1 | Value2 | Value3 |
+--------+--------+--------+
|     10 |     30 |      1 |
|     40 |     20 |     50 |
|     80 |     90 |    100 |
+--------+--------+--------+
3 rows in set (0.00 sec)

Following is the query to use CASE WHEN −

mysql> select case when (Value1=40 AND Value2=20) then Value2 else Value1 end as selectedValue from DemoTable1040;

This will produce the following output −

+---------------+
| selectedValue |
+---------------+
|            10 |
|            20 |
|            80 |
+---------------+
3 rows in set (0.00 sec)
Updated on: 2019-09-30T07:46:16+05:30

299 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements