Why does this MySQL if statement on SELECT COUNT(*) not work?

Question

I have a .bat file which executes the SQL file as follows.

The aim is that if records are found in a table, nothing will be done, whereas if the table is empty, some records will be inserted.

BEGIN 
  DECLARE rowCount INT; 
  SELECT count(*) FROM `martin1` INTO rowCount;
  IF rowCount <= 5 THEN

  END IF;
END;

But when I execute it, there is an error. I tried to delete the DECLARE, but even for (IF SELECT COUNT(*)...>0) there is still an error.

The error is,

ERROR 1064 (42000) at line 1: 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 'DECLARE rowCount INT' at line 2

How can I resolve this?

You can use this if construct only in stored programs. The same goes for SELECT ... INTO variable. Have a look at [MySQL Compound-Statement Syntax](https://dev.mysql.com/doc/refman/5.6/en/sql-syntax-compound-statements.html) — VMai, Aug 11 '14 at 15:02
What @VMai said is absolutely correct. You may want to post what you are actually trying to do? there may be an alternative. — Rahul, Aug 11 '14 at 15:07
error is "ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the ma nual that corresponds to your MySQL server version for the right syntax to use n ear 'DECLARE rowCount INT' at line 2" — martinwang1985, Aug 11 '14 at 15:14
so is it impossible for a bat file to check select count(*)? thank u — martinwang1985, Aug 11 '14 at 15:15
No it's not but you will have to wrap that code in a stored routine like stored procedure or functions. — Rahul, Aug 11 '14 at 15:15
Please explain just what you want to do inside your IF ... END IF. Probably the query could be rewritten with a HAVING clause. — VMai, Aug 11 '14 at 15:39
hi thank u all.what i want to do inside IF END IF is to insert some rows. well so,it is impossible to use a bat file to execute a sql file to realize the function that "control whether there is records in one table, if there is, do nothing, else insert some rows"? th eonly possible way is doing a stored procedure? i am not expert in mysql but i remember for stored procedure in sql server, it may be stored in database ,right? thank u very much again — martinwang1985, Aug 12 '14 at 06:39
anyway for goving value i've done SET @a=(SELECT count(*) FROM `martin1`);and it is right,but still for IF STATEMENT IF(@a>0) SELECT "A is greater tahn 0" END IF there is an error — martinwang1985, Aug 12 '14 at 07:03

score -1 · Accepted Answer · edited May 23 '17 at 12:30

-1

try this way

BEGIN 
  DECLARE rowCount INT; 
  SELECT count(*) INTO rowCount FROM `martin1`
  IF rowCount <= 5 THEN

  END IF;
END;

And have a look at this

edited May 23 '17 at 12:30

Community

1
1

answered Aug 11 '14 at 15:00

Thusitha Thilina Dayaratne

5,666
4
42
69

thank u very much for ur kindness reply.but i was told an error with this code "ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the ma nual that corresponds to your MySQL server version for the right syntax to use n ear 'DECLARE rowCount INT' at line 2" – martinwang1985 Aug 11 '14 at 15:13

Why does this MySQL if statement on SELECT COUNT(*) not work?

1 Answers1