Re: Cannot use SELECT in INSERT statement (book says I can!) - Dodger - com.mysql.lists.perl

7 messages in com.mysql.lists.perlRe: Cannot use SELECT in INSERT state...

From	Sent On	Attachments
Hal Vaughan	17 Mar 2003 23:38
Ulrich Borchers	18 Mar 2003 04:51
Dodger	18 Mar 2003 05:37
Niño Fision	18 Mar 2003 05:55
Nick Tonkin	18 Mar 2003 06:45
Falck, Greg	19 Mar 2003 13:50
Ulrich Borchers	19 Mar 2003 15:49

Subject:	Re: Cannot use SELECT in INSERT statement (book says I can!)
From:	Dodger (se...@aquest.com)
Date:	03/18/2003 05:37:21 AM
List:	com.mysql.lists.perl

Well, your solution is perfectly correct, but the sentence '"VALUES" does not belong in the query' -- well, that's actually rather a MySQL specific goof. 'VALUES' *does* belong in the query for proper SQL syntax -- inside parens -- but MySQL often goes off into its own little world, and would perhaps be better calling itself MyNSQL -- My Non-Standardised Query Language.

INSERT INTO Distro (Class) VALUES SELECT Class FROM xfer2;

"VALUES" does not belong into the query - try:

INSERT INTO Distro (Class) SELECT Class FROM xfer2;

uli

On 18 Mar 2003 at 2:39, Hal Vaughan wrote:

I'm using Sam's "Teach Yourself MySQL in 21 Days" as an introductory tutorial and guide book. I've had several problems with this (actually a lot -- I'm going out to buy a copy of the O'Reilly book on the mysql.com website when my check comes in later this week).

It has an example in the book like this:

INSERT INTO Shipper_Info (Customer_Name, Address, City, Zip) VALUES SELECT CAT(First_Name + " " + Last_Name) AS Name, Address, City, Zip FROM Customers WHERE State = "MD";

I don't have the tables set up, so I haven't tried this letter for letter, but I am trying the following:

INSERT INTO Distro (Class) VALUES SELECT Class FROM xfer2;

(xfer2 is a temp table while I'm doing transfers). I had a WHERE clause on the end (WHERE Class LIKE "%"), just to see if it was necessary, but it had no effect.

Whenever I run this line, I get an error (error 1064 You have an error in your SQL syntax near 'SELECT Class FROM xfer2 at Line 1;)

I've noticed when I get an error message that usually the error is at the start of the quoted section of a line.

So what am I doing wrong and why can't I use SELECT in an INSERT statement? It seems to be, other than adding CAT to combine 2 fields, and using a WHERE clause, that I'm doing the same thing as in the example, but no matter what I do or how I re-arrange things, it won't work.

What am I doing wrong?

Thanks!

To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail msql...@lists.mysql.com instead.