CODEWITHSUNDEEP
mysqlbatch-fileif-statement
martinwang1985
martinwang1985

Reputation: 556

if statement of mysql in text file of .sql executed by batch file

I'd like first introduce my requirement. I have a batch file which will execute a Textfile of sql. And I need the function that the program will first check the count of a table, if it is 0, then insert rows. In the following is my code. I was told 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

Allthough I have tried some other ways such as adding ";",deleting ";", but it still does not work.

SET @a=(SELECT count(*) FROM `martin1`);
IF @a>0 
THEN 
INSERT IGNORE INTO `martin1` (`col1`, `col2`) VALUES (1, 'aaa'),(2, 'bbb');
END IF;

Upvotes: 3

Views: 3580

Answers (1)

fancyPants
fancyPants

Reputation: 51888

You get the error, because you can't use control structures such as if or while in a normal query. They can only be used in stored procedures or functions. In this case you'd need a procedure. Try this:

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 execute the procedure with this statement:

CALL my_proc_name();

Note, that I replaced your COUNT(*) with EXISTS(). It's faster, cause it stops as soon as an entry is found, while COUNT() continues till it's finished counting every row.

Upvotes: 2

Related Questions