CODEWITHSUNDEEP
mysqlsubquery
julie
julie

Reputation: 55

How to use select inside if for MySQL

I got an "select is not valid at the position, expecting an expression" error for my query:

insert into tbl_disease(nid, name, diagnosed_time, treatment_times, inherited) 
values ('314759','high blood pressure','2015-07-01','4',
( if (select count(distinct disease_name) from tbl_disease where disease_name='high blood pressure') >0, 'Y', 'N'));

post I've read: MYSQL SELECT WITHIN IF Statement

Can you point out how can I correct this? Any help is appreciated! Many thanks!!!

Upvotes: 1

Views: 125

Answers (2)

ysth
ysth

Reputation: 98398

IF syntax is IF(boolean expression, result expression if true, result expression if false). You can use a comparison against a subquery returning a single value as the boolean expression, but the subquery needs to also be delimited by parentheses.

So you need to say:

if((select ...) > 0,'Y','N')

With only one ( after the if, it is expecting an expression next. (select ...) or (select ...) > 0 is an expression; select ... is not.

Adding all the missing parentheses, you should end up with:

insert into tbl_disease(nid, name, diagnosed_time, treatment_times, inherited)
values ('314759','high blood pressure','2015-07-01','4', ( if (((select count(distinct disease_name) from tbl_disease where disease_name='high blood pressure') >0), 'Y', 'N')) );

Removing the unneeded ones:

insert into tbl_disease(nid, name, diagnosed_time, treatment_times, inherited)
values ('314759','high blood pressure','2015-07-01','4', if ((select count(distinct disease_name) from tbl_disease where disease_name='high blood pressure') >0, 'Y', 'N') );

Upvotes: 1

Akina
Akina

Reputation: 42651

insert into tbl_disease(nid, name, diagnosed_time, treatment_times, inherited) 
values ('314759',
        'high blood pressure',
        '2015-07-01',
        '4',
        CASE WHEN EXISTS ( select NULL 
                           from tbl_disease 
                           where disease_name='high blood pressure' ) 
             THEN 'Y' 
             ELSE 'N'
             END);

Upvotes: 1

Related Questions