Nested MySQL select error

Question

I'm trying to make the following transaction work, but the I get a mysql error near SELECT. I've double-checked that all column names are correct.

Error message

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 articles (catid,content,title,keywords,isactive) (SELEC' at line 2

SQL

START TRANSACTION; 
INSERT INTO articles (catid,content,title,keywords,isactive) 
(SELECT 1, pendingarticles.content, pendingarticles.title, 
pendingarticles.keywords, 1 
FROM pendingarticles 
WHERE pendingarticles.id=1);
DELETE FROM pendingarticles WHERE id=1; 
COMMIT;

UPDATE

The code itself works. Both the INSERT INTO - SELECT part, and the DELETE part. Something's wrong with the transaction. Perhaps ;? Or my db server can't do transactions?

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 articles (catid,content,title,keywords,isactive) (SELEC' at line 2 — Rápli András, Jul 18 '13 at 14:06
Not sure if this will make a difference, but I don't think the `SELECT` should be in brackets. — Bernhard Barker, Jul 18 '13 at 14:07
Did you forget `VALUES`, `INSERT INTO table (....) VALUES (...);` ? — HamZa, Jul 18 '13 at 14:07
VALUES are only needed for a value list, in this case I'm selecting something form another table. — Rápli András, Jul 18 '13 at 14:08
(Of course I checked the code both ways, with and without `VALUES`) — Rápli András, Jul 18 '13 at 14:10
if you run this command `SHOW CREATE TABLE tableName` you can see what ENIGNE is used for the tables. the engine has to support transactions. — Koryu, Jul 18 '13 at 14:46
would be useful to know the engine `SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'dbname'` [How can I check MySQL engine type for a specific table?](http://stackoverflow.com/questions/213543/how-can-i-check-mysql-engine-type-for-a-specific-table) — Koryu, Jul 18 '13 at 14:56

score 2 · Accepted Answer · edited May 23 '17 at 10:25

2

MyISAM Engine does not support transactions. MyIsam engine transaction support

To support transactions you have to change the engine f.e. to InnoDB. Setting the Storage Engine

edited May 23 '17 at 10:25

Community

1
1

answered Jul 18 '13 at 15:14

Koryu

1,371
1
11
21

4

And here's the winner for me! somebody that can see past his nose before answering :D – STT LCU Jul 18 '13 at 15:17
It turns out that the server does not support innoDB, so I must continue working without this feature. Thanks for helping. – Rápli András Jul 19 '13 at 08:58

score 0 · Answer 2 · answered Jul 18 '13 at 14:10

you want:

INSERT INTO articles (catid,content,title,keywords,isactive)  
SELECT 1,pendingarticles.content,pendingarticles.title,  
        pendingarticles.keywords,1  
FROM pendingarticles  
WHERE pendingarticles.id=1;  
DELETE FROM pendingarticles WHERE id=1;

The extra set of parenthesis you provided is not necessary.

Petio Ivanov · Answer 3 · 2013-07-18T15:07:17.363

-1

I believe that the parentheses around the Select are not necessary.

http://dev.mysql.com/doc/refman/5.6/en/insert.html

START TRANSACTION; 
INSERT INTO articles (catid,content,title,keywords,isactive) 
SELECT 1, pendingarticles.content, pendingarticles.title, 
pendingarticles.keywords, 1 
FROM pendingarticles 
WHERE pendingarticles.id=1;
DELETE FROM pendingarticles WHERE id=1; 
COMMIT;

edited Jul 18 '13 at 15:07

answered Jul 18 '13 at 14:11

Petio Ivanov

305
1
5

i think you mean parenthesis () these are brackets [] – Woot4Moo Jul 18 '13 at 14:22
@Dukeling and yet the image that is there says brackets are are these things [] – Woot4Moo Jul 18 '13 at 21:31
@Woot4Moo "Parentheses ... (also called simply brackets ...". Bottom line is - you may not call them brackets, but that doesn't stop other people from being correct while calling them brackets. – Bernhard Barker Jul 19 '13 at 07:00

Nested MySQL select error

3 Answers3