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 Dec 23 '20 at 08:08 — Akina, Dec 23 '20 at 08:08

score 1 · Accepted Answer · answered Dec 23 '20 at 08:12

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 '20 at 08:12

Akina

21,183
4
9
16

Hi @akina, from this answer, it seems 'N' can never be generated, it's either Null or 'Y'. is it right? – julie Dec 23 '20 at 08:16
@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). – Akina Dec 23 '20 at 08:19
yes, thanks @Akina!!!! mucho gracias <3 – julie Dec 23 '20 at 08:19

ysth · Answer 2 · 2020-12-23T10:10:27.153

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 '20 at 10:10

answered Dec 23 '20 at 08:32

ysth

88,068
5
112
203

1

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 – julie Dec 23 '20 at 08:59
You've got an extra ) before the ,. Get rid of it or add a matching ( after if( – ysth Dec 23 '20 at 09:56
Yes Sir, but that doesn't exclude error – julie Dec 23 '20 at 10:02
thanks a lot! I think this should be the answer although the other answer is earlier. – julie Dec 24 '20 at 05:26
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 – ysth Dec 24 '20 at 06:02
yes, thank you so much! ^^ – julie Dec 24 '20 at 06:04

How to use select inside if for MySQL

2 Answers2