CODEWITHSUNDEEP
mysqlsql
Filipe Aleixo
Filipe Aleixo

Reputation: 4244

MySQL - Using SELECT for IF statement condition in stored procedure

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.

Upvotes: 1

Views: 3607

Answers (2)

greenweeds
greenweeds

Reputation: 554

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;

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269923

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.

Upvotes: 0

Related Questions