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

Question

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;

fancyPants · Accepted Answer · 2014-08-12 08:28:10Z

2

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.

answered Aug 12, 2014 at 8:28

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

martinwang1985 Over a year ago

hi.thank u very much for ur kindness reply. but i was told told in the query "CALL my_proc_name()",here is the content of my text file of .sql: CREATE DATABASE IF NOT EXISTS martintest /*!40100 DEFAULT CHARACTER SET utf8 */; USE martintest; CREATE TABLE IF NOT EXISTS martin1 ( col1 int(11) default NULL, col2 text ) ENGINE=MyISAM DEFAULT CHARSET=utf8; CALL my_proc_name(); DELIMITER $$ CREATE PROCEDURE my_proc_name() BEGIN IF EXISTS (SELECT 1 FROM martin1) THEN INSERT IGNORE INTO martin1 (col1, col2) VALUES (1, 'aaa'),(2, 'bbb'); END IF; END $$ DELIMITER ;

martinwang1985 Over a year ago

or do u mean the stored procedure should only be in the database,not in a text file? thank u

fancyPants Over a year ago

Yes, the procedure needs to be in the database. You execute the procedure before you created it in your text file. Add drop procedure if exists my_proc_name; before create procedure... and the call my_proc_name(); after that.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related