Syntax error Expression in CASE WHEN Clause doesn't work in MySQL query?

Expression in CASE WHEN Clause doesn't work in MySQL query?

Fir this, use CASE WHEN statement in MySQL correctly. Let us see how.

Let us create a table −

mysql> create table demo58
−> (
−> id int not null auto_increment primary key,
−> first_name varchar(20),
−> last_name varchar(20)
−> );
Query OK, 0 rows affected (2.15 sec)

Insert some records into the table with the help of insert command −

mysql> insert into demo58(first_name,last_name) values('John','Doe');
Query OK, 1 row affected (0.12 sec)

mysql> insert into demo58(first_name,last_name) values('David','Smith');
Query OK, 1 row affected (0.29 sec)

mysql> insert into demo58(first_name,last_name) values('John','Brown');
Query OK, 1 row affected (0.11 sec)

mysql> insert into demo58(first_name,last_name) values('David','Miller');
Query OK, 1 row affected (0.26 sec)

Display records from the table using select statement −

mysql> select *from demo58;

This will produce the following output −

+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | John       | Doe       |
|  2 | David      | Smith     |
|  3 | John       | Brown     |
|  4 | David      | Miller    |
+----+------------+-----------+
4 rows in set (0.15 sec)

Following is the query for expression in CASE WHEN −

mysql> select
−> (
−> case
−> when (first_name = "John") then 'Carol'
−> when (last_name ="Smith") then 'Doe'
−> end
−> ) RESULT
−> from demo58;

This will produce the following output −

+--------+
| RESULT |
+--------+
| Carol  |
| Doe    |
| Carol  |
| NULL   |
+--------+
4 rows in set (0.00 sec)
Updated on: 2020-11-20T07:09:22+05:30

285 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements