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

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!

I would think something like this would work:

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

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.

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

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.

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 ;

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

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

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

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.

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!