Error executing MySQL code

Question

I am using SEQUEL Pro and MySQL. When I execute each line individually no errors are generated and code works as expected. When I try to execute all lines together I get the error below.

DELIMITER $$

CREATE TEMPORARY TABLE pricesOutput (price float, namex varchar(255), updatedAt datetime);

insert into pricesOutput
select 9.99, 'Bosch ZXY', '12/05/12';

select *
from pricesOutput;

drop table pricesOutput;

end$$

delimiter;

[ERROR in query 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 'insert into pricesOutput select 9.99, 'Bosch ZXY', '12/05/12';

select *
from pr' at line 3

[ERROR in query 2] 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 'delimiter;' at line 1

Having never used sequelpro I don't know if this will work. Your insert statement seems wrong. The correct syntax for insert is; `insert into table (col, col, col) values (val, val, val), (val, val, val)`. — glend, Jul 08 '15 at 08:28
@doveyg: The syntax used by OP is correct see : https://dev.mysql.com/doc/refman/5.1/en/insert-select.html and the code runs fine in MySQL workbench — PaulF, Jul 08 '15 at 08:34
See my answer below. Is there any reason you are writing the code this way - it is normally used when defining a stored procedure — PaulF, Jul 08 '15 at 08:48

PaulF · Answer 1 · 2015-07-08T08:44:34.357

0

The DELIMITER $$ line changes the delimiter from ; to $$, so each line needs terminating with $$ instead. END is used for defining procedures so is not required & the final DELIMITER; should have a space before the semi-colon.

DELIMITER $$
CREATE TEMPORARY TABLE pricesOutput (price float, namex varchar(255), updatedAt datetime)$$
insert into pricesOutput select 9.99, 'Bosch ZXY', '12/05/12'$$
select * from pricesOutput$$
drop table pricesOutput$$
delimiter ;

edited Jul 08 '15 at 08:44

answered Jul 08 '15 at 08:38

PaulF

6,673
2
18
29

Thanks Paul, I included the delimiter definition because the example code is taken from a stored procedure. Your suggestion works for the example I gave, but not with the stored procedure. – Clive Jul 08 '15 at 19:39
DELIMITER // CREATE PROCEDURE sp_fetchPrices2() BEGIN DECLARE v_id INTEGER DEFAULT 0; DECLARE v_finished INTEGER DEFAULT 0; CREATE TEMPORARY TABLE pricesOutput (price float, namex varchar(255), updatedAt datetime); insert into pricesOutput select 9.99, 'Bosch ZXY', '12/05/12'; DECLARE cursor_price CURSOR FOR select id from products; END// DELIMITER ; – Clive Jul 08 '15 at 19:40
Glad to be of help - I did mention that END is used for defining procedures, so removed it as you said you had problems running the code - you are right that it needs to go back in when you are creating a procedure. – PaulF Jul 09 '15 at 08:54
Can you cast an eye over the code above. Works fine until the temporary table line is added. I can't see why this is causing an issue. Executes the temp table line fine by itself. Thanks – Clive Jul 09 '15 at 09:19
I think the _DECLARE cursor_price ...._ needs to go before the _CREATE TEMPORARY TABLE ...._ – PaulF Jul 09 '15 at 10:47

Error executing MySQL code

1 Answers1