Mysql Error 1064 If Statement
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up MySQL said: Error #1064 - while using IF condition in sql up vote 0 down vote favorite 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; mysql sql database share|improve this question edited Feb 26 '15 at 8:33 asked Feb 26 '15 at 7:50 Gaurav Manral 3952420 what error? this is a sql statement... –RickyA Feb 26 '15 at 7:58 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 for more details. –GarethD Feb 26 '15 at 8:00 ok @GarethD, i am updating it. well i am not very good in sql. ;) –Gaurav Manral Feb 26 '15 at 8:26 add a comment| 2 Answers 2 active oldest votes up vote 0 down vote accepted 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; share|improve this answer answered Feb 26 '15 at 8:00 Abhik Chakraborty 37k41737 still the same error –Gaurav Manral Feb 26 '15 at 8:10 Is the code is a part of stored procedure or you are trying somewhere else ? –Abhik Chakraborty Feb 26 '15 at 8:24 hey @Abhik, i am very weak in sq
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up IF THEN syntax error in My SQL (1064 error) up vote 1 down vote favorite 1 I have error while writing this qyery CREATE TRIGGER insert_Topics BEFORE INSERT ON Topics FOR EACH ROW BEGIN IF (SELECT COUNT(*) FROM Subjects WHERE ID=new.SubjectID)=0 THEN INSERT error_msg VALUES ('Foreign http://stackoverflow.com/questions/28737022/mysql-said-error-1064-while-using-if-condition-in-sql Key Constraint Violated!'); END IF; END; delimiter ; The error says 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 '' at line 8 and it points to the word THEN Any help ? Thanks in advance mysql share|improve this question edited Apr 24 '13 at 1:36 Jocelyn 7,46242242 asked Oct 16 '12 at 3:30 Mariam 1391310 add a comment| 1 Answer 1 active oldest votes up vote 1 http://stackoverflow.com/questions/12907141/if-then-syntax-error-in-my-sql-1064-error down vote accepted Both CREATE TRIGGER insert_Topics BEFORE INSERT ON Topics FOR EACH ROW BEGIN IF NOT EXISTS(SELECT 1 FROM Subjects WHERE ID=NEW.SubjectID LIMIT 1) THEN INSERT INTO error_msg VALUES ('Foreign Key Constraint Violated!'); END IF; END; and CREATE TRIGGER insert_Topics BEFORE INSERT ON Topics FOR EACH ROW BEGIN IF (SELECT 1 FROM Subjects WHERE ID=NEW.SubjectID LIMIT 1) IS NULL THEN INSERT INTO error_msg VALUES ('Foreign Key Constraint Violated!'); END IF; END; work for me, so your syntax problems probably lie elsewhere. share|improve this answer edited Oct 17 '12 at 2:59 answered Oct 16 '12 at 3:35 Ross Smith II 6,9491629 Thanks for help, but this gave another error : #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 '' at line 6 and it points to the select statment –Mariam Oct 16 '12 at 3:42 Change new to NEW and add LIMIT 1 and see if that fixed your issue. –Ross Smith II Oct 16 '12 at 8:24 Thanks. I did but it's the same error –Mariam Oct 16 '12 at 8:46 The syntax is fine, so the issue lies elsewhere. What version of MySQL are you using? The trigger works fine for me, on MySQL 5.5.27. Try commenting out the SELECT statement is see if that fixes the issue. –Ross Smith II Oct 16 '12 at 18:04 It seems so. I am working on dot5hosting MySQL and
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site http://dba.stackexchange.com/questions/119565/error-in-if-statement-in-mysql About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute: Sign up Here's mysql error how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top Error in IF statement in MySQL up vote 0 down vote favorite I am wondering why I am getting error when running below simple IF in MySQL? IF NOT EXISTS (SELECT employee_attendance_id FROM employee_attendance WHERE employee_number = 10 AND attendance_date_time = '2015-10-11 13:14:16') THEN mysql error 1064 BEGIN INSERT IGNORE INTO employee_attendance (employee_number, attendance_date_time) VALUES (10, '2015-10-11 13:14:16'); END; END IF; This is the error I am getting: [SQL] IF NOT EXISTS (SELECT employee_attendance_id FROM employee_attendance WHERE employee_number = 10 AND attendance_date_time = '2015-10-11 13:14:16') THEN BEGIN INSERT IGNORE INTO employee_attendance (employee_number, attendance_date_time) VALUES (10, '2015-10-11 13:14:16'); [Err] 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 NOT EXISTS (SELECT employee_attendance_id FROM employee_attendance WHERE empl' at line 1 Thanks, Jassim mysql share|improve this question asked Oct 29 '15 at 14:32 Jassim Rahma 183 I think this may help you. stackoverflow.com/questions/11427457/… –Gonzalo.- Oct 29 '15 at 17:51 add a comment| 1 Answer 1 active oldest votes up vote 0 down vote accepted You cannot execute such statements unless you use a stored proc: You can run this below. It should work. DROP procedure IF EXISTS `test2`; DELIMITER $$ CREATE PROCEDURE `test2`(p_employee_number int, p_date datetime) Begin if NOT EXISTS (SELECT 1 FROM employee_attendance WHERE employee_number =p_employee_number AND attendance_date_time = p_date) THEN INSERT IGNORE INTO employee_attendance (employee_number, attendance_date_time) VALUES (p_employee_number, p_date); End if ; END$$ DELIMITER ;