How to use select inside if for MySQL

Question

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!!!

Use INSERT .. SELECT instead. Or CASE WHEN EXISTS.

Akina
– Akina

2020-12-23 08:08:52 +00:00
Commented Dec 23, 2020 at 8:08 — Akina
– Akina, Commented Dec 23, 2020 at 8:08

Akina · Accepted Answer · 2020-12-23 08:12:17Z

1

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);

answered Dec 23, 2020 at 8:12

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

julie Over a year ago

Hi @akina, from this answer, it seems 'N' can never be generated, it's either Null or 'Y'. is it right?

Akina Over a year ago

@daisy No. NULL is not returned. EXISTS tests for a row presence (with any value, NULL included) and returns either TRUE (treated as 1) or FALSE (treated as 0). You may use any other literal (for example, select 1 or select 'present' - it doesn't matter).

ysth · Accepted Answer · 2020-12-23 10:10:27Z

1

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') );

edited Dec 23, 2020 at 10:10

answered Dec 23, 2020 at 8:32

ysth

99.1k6 gold badges126 silver badges220 bronze badges

4 Comments

julie Over a year ago

Thank you @ysth, when I change the subquery to if ( ( select count(disease_name) from tbl_disease where disease_name=@disease_name )>0 ), 'Y','N', errors still

ysth Over a year ago

You've got an extra ) before the ,. Get rid of it or add a matching ( after if(

julie Over a year ago

thanks a lot! I think this should be the answer although the other answer is earlier.

ysth Over a year ago

the other answer gave you a working query, but didn't explain why you got the error you did; I thought that was worth doing

Collectives™ on Stack Overflow

How to use select inside if for MySQL

2 Answers 2

2 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related