MYSQL query error

Question

What is wrong with the syntax or query?

SET @q=1000;
SET @p=5.00;

SELECT @ccount := COUNT(*) FROM 1detail WHERE price>=@p;
if(@ccount='0') THEN 
INSERT INTO 1detail (price,quantity) VALUES (@p,@q); 

ELSE

INSERT INTO 2detail (price,quantity) VALUES (@p,@q);  
END IF;

It gives me the error:

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(@ccount='0') THEN INSERT INTO 1detail (price,quantity) VALUES (@p,@q)' at line 1:

To create table (that is working):

CREATE TABLE 1detail 
(
quantity int, 
price DECIMAL(15,2) 
);

CREATE TABLE 2detail 
(
quantity int, 
price DECIMAL(15,2) 
);


INSERT INTO 1detail
(quantity, price)
VALUES
('5', '10');

Is this code inside a function or procedure or trigger?

juergen d
– juergen d

2014-07-19 21:35:05 +00:00
Commented Jul 19, 2014 at 21:35 — juergen d
– juergen d, Commented Jul 19, 2014 at 21:35

Gordon Linoff · Accepted Answer · 2014-07-19 21:37:15Z

1

Table names that start with digits need to be escaped. And, if is only used inside functions, stored procedures, and triggers. Assuming this code is "if"-safe:

SET @q=1000;
SET @p=5.00;

SELECT @ccount := COUNT(*) FROM `1detail` WHERE price>=@p;
if(@ccount='0') THEN 
INSERT INTO `1detail`(price,quantity) VALUES (@p,@q); 
ELSE
INSERT INTO `2detail`(price,quantity) VALUES (@p,@q);  
END IF;

If not, you can just do this as two inserts:

INSERT INTO `2detail`(price,quantity)
    VALUES (@p, @q)
    WHERE exists (select 1 from `detail` where price >= @p)

INSERT INTO `1detail`(price,quantity)
    VALUES (@p, @q)
    WHERE not exists (select 1 from `detail` where price >= @p)

answered Jul 19, 2014 at 21:37

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

MYSQL query error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related