MySQL - Using SELECT for IF statement condition in stored procedure

Question

I want to execute, in a stored procedure, a certain set of statements if, in table my_table there is exactly one row with value value in column column_name. I have tried the following, but I get a syntax error:

IF ((SELECT COUNT(*) FROM my_table WHERE column_name = value) = 1) THEN
    BEGIN
    END;
END IF;

For context: In my procedure I create a temporary table at some point, where I store a list of values. Then later on in the procedure, I want to check if a given value is present in that temporary table.

MySQL only supports the IF statement in programming blocks such as stored procedures. You cannot write code this way. — Gordon Linoff
– Gordon Linoff, Commented Mar 5, 2018 at 13:08
Well, with a then, the syntax is correct and this code should work (if used correctly in a procedure, e.g. you added the delimiter and such). Adding the errormessage and more code (since that cannot be all there is, as it should not generate an error, so the problem will likely lie somewhere else) will be the next step to identify the problem. — Solarflare
– Solarflare, Commented Mar 5, 2018 at 13:35

greenweeds · Accepted Answer · 2018-03-05 13:31:34Z

4

I think you might be better to structure it more like this

BEGIN
DECLARE myCOUNT INTEGER;

SELECT COUNT(*) 
INTO myCount
FROM my_table
WHERE column_name=value; 

IF (myCount = 1) THEN
 -- do stuff
END IF;

END;

edited Mar 5, 2018 at 13:31

answered Mar 5, 2018 at 13:29

greenweeds

5742 silver badges13 bronze badges

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

2 Comments

Filipe Aleixo Over a year ago

I was thinking about doing it this way. But was just wondering if there was a way to do it without creating an auxiliary variable.

greenweeds Over a year ago

I think the only other way to do it would be to use a CURSOR and FETCH - but given you only ever return one row from that query, that's more complicated without any real benefit. dev.mysql.com/doc/refman/5.7/en/cursors.html - I don't think MySQL supports implicit cursors, and they are generally frowned on anyway (FOR i IN SELECT COUNT(*) FROM ...)

Gordon Linoff · Accepted Answer · 2018-03-05 13:13:12Z

0

I'm not sure what you are trying to do, but I'll guess an "upsert" -- update a record if it exists, otherwise insert a new record.

In any case, if you are trying to ensure that name is unique in my_table, then this is not the right approach at all. Instead, declare a unique index/constraint so the database ensures the data integrity:

create unique index unq_my_table_name on my_table(name);

You can then use insert . . . on duplicate key update to modify the records in the database.

answered Mar 5, 2018 at 13:13

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Filipe Aleixo Over a year ago

In my procedure I create a temporary table at some point, where I store a list of values. Then later on in the procedure, I want to check if a given value is present in that temporary table.

Collectives™ on Stack Overflow

MySQL - Using SELECT for IF statement condition in stored procedure

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related