mysql error on run query

Question

This is my query in mysql (I use phpmyadmin to execute the query). but I can't execute.... can help me please?

SELECT count(*) into @cnt FROM `oie_option` WHERE `opt_name` =CONCAT('earning1391',pmonth('2015-09-09')))
if(@cnt <=0)then
INSERT INTO oie_option ('opt_name','opt_value') VALUES (CONCAT('earning1391',pmonth('2015-09-09')),1000);
end if;

And this is the error:

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(@cnt <=0)then insert into oie_option ('opt_name','opt_value') values(CON' at line 1

Why? Thanks

You can not use if-else etc in the query they are only allowed in stored function, procedures or triggers. — Abhik Chakraborty, Sep 14 '15 at 07:38
thanks, exactly i use this query in triger creation.... and return this error — javad naroogheh, Sep 14 '15 at 07:52
Get rid off single quotes in the column names `('opt_name','opt_value')` and should be `(opt_name,opt_value)` — Abhik Chakraborty, Sep 14 '15 at 09:00

Tarun Upadhyay · Answer 1 · 2015-09-14T08:11:02.370

0

I think you are missing a semicolon in first query.

    SELECT count(*) into @cnt FROM `oie_option`
     WHERE `opt_name` =CONCAT('earning1391',pmonth('2015-09-09'));
    if(@cnt <=0)then
    INSERT INTO oie_option ('opt_name','opt_value') VALUES 
     (CONCAT('earning1391',pmonth('2015-09-09')),1000);
     end if;

edited Sep 14 '15 at 08:11

answered Sep 14 '15 at 07:56

Tarun Upadhyay

724
2
7
16

score 0 · Answer 2 · edited May 23 '17 at 11:51

I tryed to reproduce your situation and this is what I got:

You need to put your code inside of a strored procedure or function to use the IF statement https://stackoverflow.com/a/12954385/5308054. Here you can see a feature request to fix it https://bugs.mysql.com/bug.php?id=48777

You could avoid using the if statement with query like this

INSERT INTO oie_option ('opt_name','opt_value') 
SELECT CONCAT('earning1391',pmonth('2015-09-09')),1000
FROM oie_option
WHERE (SELECT count(*) FROM `oie_option` WHERE `opt_name`=CONCAT('earning1391',pmonth('2015-09-09')))=0
LIMIT 1;

score 0 · Accepted Answer · edited Jul 20 '22 at 17:49

0

I declared the variable and corrected.

DECLARE cnt INT DEFAULT 0;
 SELECT count(*) INTO cnt FROM `oie_option` WHERE `opt_name` = CONCAT( 'earning1391', pmonth (now()));

edited Jul 20 '22 at 17:49

General Grievance

4,555
31
31
45

answered Sep 16 '15 at 05:26

javad naroogheh

121
7

mysql error on run query

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

3 Answers3