CODEWITHSUNDEEP
mysqlsqlif-statement
user3780856
user3780856

Reputation: 45

MYSQL query error

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

Upvotes: 0

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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)

Upvotes: 1

Related Questions