MySQL said: Error #1064 - while using IF condition in sql

Question

I got this error msg in my sql command. i am unable to figure out the cause. please help me.

if ( SELECT * FROM `teams` WHERE `client_id`='3' and `member_id`='6' and `current`='1' ) then
begin 
UPDATE `teams` SET `current`='0' WHERE `client_id`='3' and `member_id`='6' and `current`='1'
end ;
else
begin
INSERT INTO `teams`(`client_id`, `member_id`) VALUES ('3','33')
end;
end if;

here i'm trying to update column current if set to 1, else insert a new record.

I need to use this sql command in my php file when a form is submitted.

below is the error message:

#1064 - You have an error in your SQL syntax;

As far as I know in MySQL you can only use `IF .. ELSE` inside a stored procedure or function. You also can't use `EXISTS` in this way, you would need to use `IF (SELECT EXISTS (SELECT * FROM teams))` - See [this answer](http://stackoverflow.com/q/1676551/1048425) for more details. — GarethD, Feb 26 '15 at 08:00
ok @GarethD, i am updating it. well i am not very good in sql. ;) — Gaurav Manral, Feb 26 '15 at 08:26

score 0 · Accepted Answer · answered Feb 26 '15 at 08:00

0

This is not a valid statement in mysql, the valid syntax would be as below. Also if-else is allowed in stored procedure, functions or triggers.

if ( SELECT * FROM `teams` WHERE `client_id`='3' and `member_id`='6' and `current`='1' ) then
 begin 
   UPDATE `teams` SET `current`='0' WHERE `client_id`='3' and `member_id`='6' and `current`='1'
 end ;
else
 begin
    INSERT INTO `teams`(`client_id`, `member_id`) VALUES ('3','33')
 end;
end if;

answered Feb 26 '15 at 08:00

Abhik Chakraborty

44,654
6
52
63

Is the code is a part of stored procedure or you are trying somewhere else ? – Abhik Chakraborty Feb 26 '15 at 08:24
hey @Abhik, i am very weak in sql, so i have no idea about `stored procedures`. I need to use above sql command in my php file when a form is submitted. However i am trying to use it directly in phpmyadmin for testing. – Gaurav Manral Feb 26 '15 at 08:30
No this would't work in regular mysql via PHP. if-then-begin ... they are syntax of stored procedure or trigger. You can not have that in mysql directly. – Abhik Chakraborty Feb 26 '15 at 08:44
ok @Abhik. so how can i do the same in my php file. any suggestions..?? – Gaurav Manral Feb 26 '15 at 08:59
Well you need to create a prodedure using PHP and then execute it. Check the tutorial here http://php.net/manual/en/mysqli.quickstart.stored-procedures.php – Abhik Chakraborty Feb 26 '15 at 09:01
Hello Abhik. I tried doing that but got an error. can you please solve this. I am giving my code as an answer, please have a look. – Gaurav Manral Feb 26 '15 at 09:54
Not sure on PHP end, try removing the `;` after the end and see how it goes. – Abhik Chakraborty Feb 26 '15 at 10:11
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/71844/discussion-between-gaurav-manral-and-abhik-chakraborty). – Gaurav Manral Feb 27 '15 at 05:26
i created the stored procedure. it is working also, but either if or else condition is working at a time while i have written it in a while loop. can you figure out the error here http://stackoverflow.com/questions/28758504/if-else-condition-error-in-stored-procedure. – Gaurav Manral Feb 27 '15 at 06:00
it helped me a lot, so i am marking it as the answer. thanks for your support dear. – Gaurav Manral Feb 27 '15 at 07:54

score 0 · Answer 2 · answered Feb 26 '15 at 08:19

0

The whole set of statements can be done with one INSERT...ON DUPLICATE KEY UPDATE... statement.

answered Feb 26 '15 at 08:19

Rick James

135,179
13
127
222

please suggest an example or query. – Gaurav Manral Feb 26 '15 at 08:21

MySQL said: Error #1064 - while using IF condition in sql

2 Answers2