MySql IF statement in query

Question

I just finished picking up my jaw off the floor after learning that you can't use an IF statement in a query in MySql. How can anyone get anything done without an IF statement?!

What I'm trying to do is write a DML script that adds a constraint to a table, if it doesn't exist. Something like this:

if (select count(*) from information_schema.table_constraints
    where constraint_name='fk_user_user_status') = 0
then
    alter table `user`
        add constraint fk_user_user_status foreign key (status_id)
            references user_status(id);
end if;

How would one do this in MySql?

Thanks in advance!

related: stackoverflow.com/questions/3919226/…

Marat Salikhov
– Marat Salikhov

2011-03-26 08:54:26 +00:00
Commented Mar 26, 2011 at 8:54 — Marat Salikhov
– Marat Salikhov, Commented Mar 26, 2011 at 8:54

Laith · Accepted Answer · 2011-03-26 08:53:52Z

3

You can only run IF statements in stored procedures and functions.

http://dev.mysql.com/doc/refman/5.0/en/if-statement.html

If you are executing standard scripts, then IF statements only work with SELECT queries.

http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_if

answered Mar 26, 2011 at 8:53

Laith

6,0911 gold badge36 silver badges62 bronze badges

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

Comments

RichardTheKiwi · Accepted Answer · 2011-03-26 09:54:39Z

2

Procedural batch, but within the below, a proc is created and dropped after the operation completes.

delimiter $$

drop procedure if exists tmp_add_fk $$

create procedure tmp_add_fk()
begin
if (select count(*) from information_schema.table_constraints
    where constraint_name='fk_user_user_status') = 0
then
    alter table `user`
        add constraint fk_user_user_status foreign key (status_id)
            references user_status(id);
end if;
end$$

delimiter ;

call tmp_add_fk;

drop procedure tmp_add_fk;

answered Mar 26, 2011 at 9:54

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

3 Comments

alekop Over a year ago

Awesome! Thanks, Richard! I was just thinking about doing the same. One question, though: what are those $$'s and delimiters for?

RichardTheKiwi Over a year ago

@alekop You could almost get away without changing the delimiter since the proc finishes in one statement. But it allows you to have multi-statement procs (containing ;) which would otherwise end the proc definition as soon as it sees ;. e.g. delimiter $$ changes it so that statements only stop at $$.

alekop Over a year ago

Got it, thanks! This is only necessary when working through the mysql shell.

Collectives™ on Stack Overflow

MySql IF statement in query

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related