MySQL – Using IF Statement Outside Stored Procedures

MySQL

I want to call a query to update a mysql table but my query contains an if statement

set @id = 0;
set @the_number = 0;
set @the_message = 0;
set @selected_message = 0;

SELECT id, dest_msisdn, text_message INTO @id, @the_number, @the_message FROM    incoming_sms where service_id = 6015592000101762 AND job_status = 0 limit 1;

if(@the_message LIKE '%Bank%')then

select 'h';

/* Update Statement Here*/
else 

select 'nothing to see here';

end if;

This is the error that i get.

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'if(@the_message LIKE '%Bank%')then

select 'h'' at line 1

In the mysql manual found here for if http://dev.mysql.com/doc/refman/5.0/en/if.html
it explains The IF statement for stored programs implements a basic conditional construct....

Are If statements usable outside stored procedures?.

Best Answer

Use the IF() function

mysql> set @the_message = 'Rolando Bank of America';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT IF(@the_message LIKE '%Bank%','h','nothing here to see') result;
+--------+
| result |
+--------+
| h      |
+--------+
1 row in set (0.00 sec)

mysql> set @the_message = 'Rolando Bk of America';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT IF(@the_message LIKE '%Bank%','h','nothing here to see') result;
+---------------------+
| result              |
+---------------------+
| nothing here to see |
+---------------------+
1 row in set (0.00 sec)

mysql>

Give it a Try !!!

Related Question