Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at NCDevCon 2011 (Raleigh, NC) with:

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

By Ben Nadel on
Tags: SQL

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.


Looking For A New Job?

100% of job board revenue is donated to Kiva. Loans that change livesFind out more »

Reader 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 -

Reply to this Comment

@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.

Reply to this Comment

@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.

Reply to this Comment

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.

Reply to this Comment

@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.

Reply to this Comment

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.

Reply to this Comment

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.

Reply to this Comment

@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.

Reply to this Comment

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!!

Reply to this Comment

@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.

Reply to this Comment

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

Reply to this Comment

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.

Reply to this Comment

@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.

Reply to this Comment

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 ;

Reply to this Comment

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 ;

Reply to this Comment

@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.

Reply to this Comment

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

Reply to this Comment

@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)

Reply to this Comment

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)

Reply to this Comment

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.

Reply to this Comment

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';

Reply to this Comment

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... :)

Reply to this Comment

@Itai,

Yeah, unfortunately, I've learned to just split my complex SQL up a bit to cope with this.

Reply to this Comment

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.

Reply to this Comment

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

Reply to this Comment

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;

Reply to this Comment

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

Reply to this Comment

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

Reply to this Comment

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#">

Reply to this Comment

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.

Reply to this Comment

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.

Reply to this Comment

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!

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.