0

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

1

1 Answer 1

0

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 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.