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
– Rápli András, Commented Jul 18, 2013 at 14:06
Did you forget VALUES, INSERT INTO table (....) VALUES (...); ? — HamZa
– HamZa, Commented Jul 18, 2013 at 14:07

Community · Accepted Answer · 2017-05-23 10:25:34Z

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, 2017 at 10:25

CommunityBot

11 silver badge

answered Jul 18, 2013 at 15:14

Koryu

1,3811 gold badge11 silver badges21 bronze badges

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

2 Comments

STT LCU Over a year ago

And here's the winner for me! somebody that can see past his nose before answering :D

Rápli András Over a year ago

It turns out that the server does not support innoDB, so I must continue working without this feature. Thanks for helping.

Woot4Moo · Accepted Answer · 2013-07-18 14:10:18Z

0

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.

answered Jul 18, 2013 at 14:10

Woot4Moo

24.4k19 gold badges103 silver badges156 bronze badges

Comments

Petio Ivanov · Accepted Answer · 2013-07-18 15:07:17Z

-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, 2013 at 15:07

answered Jul 18, 2013 at 14:11

Petio Ivanov

3051 silver badge5 bronze badges

3 Comments

Woot4Moo Over a year ago

i think you mean parenthesis () these are brackets []

Woot4Moo Over a year ago

@Dukeling and yet the image that is there says brackets are are these things []

Bernhard Barker Over a year ago

@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.

Collectives™ on Stack Overflow

Nested MySQL select error

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related