MySQL error while using IF-ELSE.?

Question

Here is my query which I want to run, but I'm getting an error. Help!?
************
Query
**************
SELECT
@rowCount := COUNT(*)
FROM
mess1veg
WHERE
Name = 'test';


IF (@rowCount >0) THEN

UPDATE mess1veg SET Name='b0' WHERE Name='test';

ELSE

INSERT into mess1veg(User_id,Name,Breakfast,Lunch,Dinner,Total,Time) values('b110010','test','$mess1vegbf','$mess1veglunch','$mess1vegdinner','$mess1veg_total',NOW());

END IF;



**********
Error
**********
SQL query:

IF( @rowCount >0 ) THEN UPDATE mess1veg SET Name = 'b0' WHERE Name = 'test';


MySQL said:

#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 (@rowCount >0) THEN

UPDATE mess1veg SET Name='b0' WHERE Name='test'' at line 1

Answer 1

Well, do it differently:

IF EXISTS (SELECT * FROM mess1veg WHERE Name= 'test') THEN

UPDATE mess1veg SET Name='b0' WHERE Name='test';

ELSE

INSERT into mess1veg(User_id,Name,Breakfast,Lunch,Di… values('b110010','test'...);

END IF;



OR if Name is unique(PK?) use



INSERT into mess1veg(User_id,Name,Breakfast,Lunch,Di… values('b110010','test'...);

ON DUPLICATE KEY UPDATE Name='b0' ;

Answer 2

Mysql If Else

Answer 3

Why would you try to turn SQL into a procedural language?



Insert no matter the count. This is a sign of bad design. You are trying to prevent update anomalies by skirting around how the table should be set up. Yuck, yuck, yuck, I imagine Joe Celko would say.



If you really have to do a subquery, I'm pretty sure mysql allows a select clause in the update statement.

Answer 4

Mysql is good for writing basic queries.



If you want write stored procedures, triggers etc use ORACLE DB.