If statement mysql in .sql text file executed by batch file
I would like to present my claim first. I have a batch file that will execute an sql text file. And I need a function for the program to check the number of columns first if it is 0, then insert the rows. Below is my code. I was informed about the error:
RROR 1064 (42000) at line 15: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
nnear 'IF @a>0
THEN
INSERT IGNORE INTO `martin1` (`col1`, `col2`) VALUES (1, 'aaa'),(' at line 1
Although I've tried other ways like adding ";", removing ";", but it still doesn't work.
SET @a=(SELECT count(*) FROM `martin1`);
IF @a>0
THEN
INSERT IGNORE INTO `martin1` (`col1`, `col2`) VALUES (1, 'aaa'),(2, 'bbb');
END IF;
source to share
You are getting an error because you cannot use control structures like if
or while
in a normal query. They can only be used in stored procedures or functions. In this case, you will need a procedure. Try the following:
DELIMITER $$
CREATE PROCEDURE my_proc_name()
BEGIN
IF EXISTS (SELECT 1 FROM marting1) THEN
INSERT IGNORE INTO `martin1` (`col1`, `col2`) VALUES (1, 'aaa'),(2, 'bbb');
END IF;
END $$
DELIMITER ;
Then follow the procedure with this statement:
CALL my_proc_name();
Note that I've replaced yours COUNT(*)
with EXISTS()
. This is faster because it stops as soon as a record is found and COUNT()
continues until it finishes reading each line.
source to share