Skip to main content
Ben Nadel at CFUNITED 2010 (Landsdown, VA) with: Michelle Meadows
Ben Nadel at CFUNITED 2010 (Landsdown, VA) with: Michelle Meadows

MySQL Does Not Support IF / ELSE Statements In General SQL Work Flow

By on
Tags:

This is just a minor note, but it took me a really long time to debug, so I thought I would put it up here. Apparently, IF statements and ELSE statements are not allowed in the general SQL processing flow of MySQL scripts. They are only allowed in functions and stored procedures. Looking back at my research, that makes sense - you can only ever find documentation on the IF statement in the context of stored procedures and functions; however, the document is really really poor at announcing that (I guess it just assumes you are reading the entire MySQL manual from start to end or something).

I never found a great work-around to this problem and resorted to breaking my logic up into two different CFQuery tags. It feels hacky, but it gets the job done.

Reader Comments

153 Comments

What's the SQL? You can't say you've got a SQL dilemma and then not say what it is! That's like leaving a half a comment. I leaves everyone in antici -

15,640 Comments

@Rick,

Sorry about that. I didn't have an example on hand, but I probably wanted to do something like this:

<cfquery>

SET @contact_id = COALESCE(
(
SELECT c.id FROM contact c WHERE c.email = #FORM.email#
),
0
);

<!--- Check to see if contact was found in system. --->
IF (@contact_id = 0) THEN

INSERT .....
SET @contact_id LAST_INSERT_ID();

ENDIF

<!--- At this point, the @contact_id value is valid, whether new or old. --->

.... do more processing using @contact_id....
</cfquery>

Probably something like that. Like I said, I simply broke it up into several queries and it works fine.

I am not saying that I would always do something like this, but I think I was in the middle of a data-cleaning script and I tend to put a lot of processing logic into scripts of that nature.

15,640 Comments

@Joe,

The problem with that is that in order to get the LAST_INSERT_ID(), I would have to run the INSERT; however, I don't want to run the insert if the @contact_id is not zero.

153 Comments

Can you cheat and do it backwards?

INSERT INTO foo
SELECT a.*
FROM ( SELECT 'literal', 'literal', ... ) AS a
LEFT OUTER JOIN foo AS b ON (...)
WHERE b.id IS NULL

That is, join your insert data with the table you are about to insert on, and if there's a match then don't actually insert anything. You can then safely run a normal lookup to find the id of the record as it doesn't matter what the last inserted ID was.

12 Comments

@Ben

Whoops, I misread. You could always make your insert into a function, but it's a hack and it still involves you writing a function.

44 Comments

to be honest, this is a good thing.

less not forget that conditions are business logic and as such should be placed in stored procedures. i know there are people out there that will disagree but, facts are facts.

1 Comments

Hey Ben,

Check out INSERT ... ON DUPLICATE KEY UPDATE (I'm not sure how to post a link in comments - http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html).

Something like:

INSERT INTO contact (id,name,email) VALUES (x,y,z)
ON DUPLICATE KEY UPDATE 0=0;

If you'd like to actually update the fields (rather than insert) you can change 0=0 to whatever fields you'd like to change.

Not sure if this is exactly what you're looking for, but it might do the trick.

15,640 Comments

@Rick,

That's pretty clever. For readability, I think i'll just stick to the multiple queries. I still have yet to read that SQL for Mere Mortals book. I gotta get my chops in order.

@Tony,

A stored procedure is still the Database. Shouldn't business logic go in your application layer? Not that mine is good with logic like that in the raw SQL... but.

@Duncan,

I tried that, but no luck. CASE I think can only be used as part of another statement, not it's on entity.

@Andrew,

I've never used that (although I've seen some people demo it). The problem we run into in the current application is that we are doing logical deletes, so really there is nothing unique about each row except the auto-incrementing value. And, since that gets created automatically, I can't force a PKEY duplication error. But otherwise, that would be cool.

7 Comments

Hey Ben,

I think (I'm not 100% on this) that you can force a duplicate row by just making email a unique index (not part of the PK). For the logical deletes you might be out of luck.

Another question to consider, how core is this piece of code to your process? It may be better to just use the multiple queries (for both performance and maintainability).

Just a thought, good luck!!

15,640 Comments

@Andrew,

It's not core at all. In fact, I was only writing it cause I creating scripts that would transfer data from an old ACCESS database into a MySQL database. These were one-off scripts, never to be used again once the transformation was complete.

1 Comments

I would like to include if logic into my stored proc as follows:

CREATE PROCEDURE `ABCD`( IN A VARCHAR (25),
IN B VARCHAR (25),
IN C VARCHAR (75),
IN D VARCHAR (25) )
BEGIN
SELECT X
FROM Y
WHERE Z = A

IF X isDefined then return X

ELSE
SELECT M
FROM N
WHERE P = B

IF Y isDefined then return Y

ELSE
ETC

END$$

Thanks in advance

5 Comments

Thank you so much for putting an end to my hours of misery. I too searched for hours trying to do something as simple as an IF STATEMENT in a script. Dear Lord I cannot WAIT to get our company off this amateur hour engine.

15,640 Comments

@Kale,

I know what you mean. Switching from MS SQL server to MySQL at my current company was a bit of switch. There is some stuff about MySQL that I like, like the LIMIT statement, but there are definitely a number of things that I miss about SQL Server.

3 Comments

For my version (5.0.32-Debian_7etch3-log
) this works for me:

DELIMITER $$

DROP PROCEDURE IF EXISTS `dbname`.`update_op`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `update_table`(_i_contactid INT)
BEGIN
IF (_i_contactid IS NULL) THEN
/* do something with NULL param*/
ELSE
/* do something else param is not NULL*/
END IF;
END$$

DELIMITER ;

3 Comments

Rats - didn't check the code - ignore above this will work:

DELIMITER $$

DROP PROCEDURE IF EXISTS `update_op`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `update_op`(_i_contactid INT)
BEGIN
IF (_i_contactid IS NULL) THEN
/* do something with NULL param*/
Select 5;
ELSE
/* do something else param is not NULL*/
Select _i_contactid;
END IF;
END$$

DELIMITER ;

15,640 Comments

@Brian,

Correct - IF/ELSE works within procedures and functions (which I believe is what you're defining). I wish it worked in the general workflow of a standard query.

3 Comments

Hi Ben,

That is exactly what I was trying to define. I started reading from the bottom up ( my excuse is that It was late ) and was really trying to help out Mike - who, like me, should have read the intro paragraph fully.

Cheers

1 Comments

@Joe Zack,
Sep 2, 2008 at 11:58 AM
I would think something like this would work:

IF(@contactID = 0, @LAST_INSERT_ID(), @contactID)

referring what u wrote here, i would like to know
is that possible to get something like:
if ( true/false, true) elseif (true/false, true) elseif (true/false, true, false)

if ( true/false, true) elseif (true/false, true) else ( false)

2 Comments

create table person(
person_id int,
person_name varchar(100)
);

create table place(
place_id int,
place_name varchar(100)
);

create table eav(
eav_id int,
entity varchar(100),
attribute varchar(100),
int_value int,
text_value varchar(100),
display_value varchar(100)
);

insert into person values (1, 'JOHN');
insert into person values (2, 'COLLEEN');
insert into place values (1, 'EAST LYME');
insert into place values (2, 'GROTON');

insert into eav values (1, 'person', 'who', 1, null, null);
insert into eav values (2, 'place', 'home', 1, null, null);
insert into eav values (3, 'person', 'who', 2, null, null);
insert into eav values (4, 'place', 'home', 1, null, null);
insert into eav values (5, null, 'comment', null, null, 'this is a comment');

select
eav.eav_id,
if(eav.entity = 'person', person.person_name,
if(eav.entity = 'place', place.place_name,
eav.display_value)) display
from
eav
left outer join person on (eav.int_value = person.person_id)
left outer join place on (eav.int_value = place.place_id)

1 Comments

Hi guys,

I'm not sure whether someone could help me with the problem I have.
I have the following table:

nav_id
nav_name
nav_limit

This table stores the navigations of the website and is then linked to the joing table etc.
Anyway - what I'm trying to do is to limit the number of displayed links in the navigation if the value in the nav_limit cell is more than 0.
By default nav_limit has value of 0.

I tried the following SQL statement but it doesn't work - it throws some errors:

SELECT * FROM
tbl_pages AS tbl1,
tbl_pages_navigation AS tbl2,
tbl_navigations AS tbl3
WHERE tbl1.pg_id = tbl2.pn_page
AND tbl2.pn_navigation = $v
AND tbl2.pn_navigation = tbl3.nav_id
ORDER BY tbl2.pn_order ASC
IF tbl3.nav_limit > 0 THEN LIMIT tbl3.nav_limit ENDIF

Could someone help me with this one as I'm stack and don't really know how to solve it.

Many thanks.

1 Comments

If I understand it correctly you want to do this without using procedures or functions but don't mind using multiple sql statements. I'm not sure what a CFQuery is, but I think the code below does what you want. Note the use of the special mySQL DUAL table. If you replace this with the contact table it will work except when the table is empty.

set @new_email = 'me@there.com';
set @contact_id = 0;
insert into contact (email) select (@new_email) from DUAL where not exists (select id into @contact_id from contact where email=@new_email) limit 1;
set @contact_id = if (@contact_id = 0,LAST_INSERT_ID(),@contact_id);

/* Display result */
select @new_email as 'E mail', @contact_id as 'Contact ID';

2 Comments

Thanks, man.
6 months later I'm trying to do the same thing, going through the exact same problem, not understanding why IF doesn't work in my direct SQL despite following the documentation bit by bit.
Too bad you're not the first result in Google... :)

2 Comments

Yeah, I'm in the middle of converting a SQL Server based system to MySQL. Finding out there are in fact some thing Microsoft does better ;) Pricing model ain't one of them, though.

1 Comments

I have come across one case for doing this which is not related to business logic. I want to test the version of the MySQL server to determine if it supports a certain feature, like CREATE EVENT, before creating the event. In theory, this would allow me to write a query that could cleanly create a certain event, or not if EVENTs are not supported, on multiple servers. I would still like to see this functionality someday...

-Peter

1 Comments

I also has spent a lot of time trying to find approach for if/esle script. Thanks for this article. No, I have stopped to investigate the problem and accepted the approach with stared procedures like:

main_procedure()
begin
if ... then call pceedure1;
else if ... then call pceedure2;
else if ... then call pceedure3;
endif
end;

1 Comments

@George: That example is creating a stored procedure to be used by the trigger created, not actually running any statments.

1 Comments

You cannot believe how long I've looked for this.

And the response from @Tony Pattruzzi is shear mssql bigotry.

An if test is not "Business Logic". There's no such thing as "Business Logic". There's only "Logic".

Conditional branching is part and parcel of programming, and scripting is programming.

Simple things like "Did I already do this once before?" become nearly impossible without some sort of if-then-else.

For instance, I was simply writing a "seed data" script for our testers.

If there was any real data in the database, I wanted to print a message, not truncate the tables, and exit. If there was no real data, then I'd truncate and load.

This nonsense about "do everying in a proc or a function" is asinine.

My frustration leaks through.

I'm glad you documented it; now I can move on.

Peace,
-john

3 Comments

I ran into this exact situation. I had already tried many of the suggestions listed here. I decided to go strictly CF and create a CF created query with the output from a MySQL query. This allowed me to do If/Else or anything I wanted with the data from the DB and then populate the CF created query with the massaged data. The CF created query is the final output. I use this in a CFC as the output to a CF Grid bind call. I needed to massage the data *before* it got to the CF Grid. (having trouble submitting code with comment so I put a * in each disallowed word(s) :)
Example:
(CF Query here)
<cf*set new*Query = query*New("final_column_1, final_column_2","Integer, Var*Char")>

<cf*loop index="i" from="1" to="10">
(manipulate data from CF*Query above here, ex. <cf*set second*Col*New*Value = "this_is_col_2_new_value_#col_from_CF*Query*Above#")<
<cf*set query*Add*Row(new*Query)>
<cf*set query*Set*Cell(new*Query, "final_column_1", i)>
<cf*set query*Set*Cell(new*Query, "final_column_2", "#second*Col*New*Value#")>
</cf*loop>

<cf*dump var="#new*Query#">

2 Comments

if (mysql_query("SELECT * FROM skaitliukas"))
{
echo '[b]First[/b]<br/>';
$a1=mysql_query("SELECT * FROM skaitliukas WHERE ip='" . IP . "' AND narsykle='" . NARSYKLE . "'");
if (($a1) and (mysql_num_rows($a1) != 0))
{
echo '[b]Second[/b]<br/>';
$a2=mysql_query("SELECT * FROM skaitliukas WHERE ip='" . IP . "' AND narsykle='" . NARSYKLE . "' AND data != '" . DATA . "'");
if (($a2) and (mysql_num_rows($a2) != 0))
{
echo '[b]Third[/b]<br/>';
}
}else{mysql_query("INSERT INTO skaitliukas(ip,narsykle,versija,nr) VALUES ('IP','NARSYKLE','WAP',1)");}
mysql_query("DELETE FROM skaitliukas WHERE data != '" . DATA . "'");
}else{include VIETA . "priedas/lenteles/skaitliukas.php";}

In that script I cant't insert into [b]skaitliukas[/b] table, i hate that, 5 hours i tryed and nothing helpful.

In else can't insert into mysql table. :(

MySQL have wery big fault, mayby bug better to say. For that txt data bases have 1 better side.

2 Comments

if (mysql_query("SELECT * FROM skaitliukas"))
{
echo '[b]First[/b]<br/>';
$a1=mysql_query("SELECT * FROM skaitliukas WHERE ip='" . IP . "' AND narsykle='" . NARSYKLE . "'");
if (($a1) and (mysql_num_rows($a1) != 0))
{
echo '[b]Second[/b]<br/>';
$a2=mysql_query("SELECT * FROM skaitliukas WHERE ip='" . IP . "' AND narsykle='" . NARSYKLE . "' AND data != '" . DATA . "'");
if (($a2) and (mysql_num_rows($a2) != 0))
{
echo '[b]Third[/b]<br/>';
}
}else{mysql_query("INSERT INTO skaitliukas(ip,narsykle,versija,nr) VALUES ('IP','NARSYKLE','WAP',1)");}
mysql_query("DELETE FROM skaitliukas WHERE data != '" . DATA . "'");
}else{include VIETA . "priedas/lenteles/skaitliukas.php";}

In that script I cant't insert into [b]skaitliukas[/b] table, i hate that, 5 hours i tryed and nothing helpful.

In else can't insert into mysql table. :(

MySQL have wery big fault, mayby bug better to say. For that txt data bases have 1 better side.

1 Comments

OMG thank you for spelling it out! I was beginning to suspect this but could not find anyplace the specified that IF/THEN was only valid within a stored procedure! I kept going crazy trying to test IF statements out by hands. I have to create indices for all tables, but you can't create an index using a variable! So I tried unrolling the loop, but I also must test if the index exists too, so I need IF/THEN!

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel