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

Posted September 2, 2008 at 10:14 AM by Ben Nadel

Tags: SQL

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 -

- pation!

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

I would think something like this would work:

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

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

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.

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

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.

Can you just use the CASE statement?

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.

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

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

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

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

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.

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

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 ;

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 ;

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

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

@Brian,

No worries :) I'm never afraid of having *too much* good information on my blog / 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)

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)

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.

@Mark,

Have you considered breaking it out into two queries? I think that might be easier.

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

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

@Itai,

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

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.

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

Wow !
seriously thank you so much. was struggling so much. grate help.

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;

The documentation for triggers shows an example of using an if statement directly in it but by my testing, this does not actually work (nor does begin/end)

http://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html

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

This is the current cap grids, new products and special offers. this information is also included in that file.
http://www.bigapplecard.com//-c-229.html

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

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

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.

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.

Post A Comment

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.

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:	Supported HTML tags for formatting: <strong>bold</strong>   <em>italic</em>   <code>code</code>
	Remember my information
	Subscribe to comments
	Send me a copy of this comment