Cannot use SELECT in INSERT statement (book says I can!)

I'm using Sam's "Teach Yourself MySQL in 21 Days" as an introductory tuto=
rial=20
and guide book. I've had several problems with this (actually a lot -- I=
'm=20
going out to buy a copy of the O'Reilly book on the mysql.com website whe=
n my=20
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 =3D "MD";

I don't have the tables set up, so I haven't tried this letter for letter=
, but=20
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=20
the end (WHERE Class LIKE "%"), just to see if it was necessary, but it h=
ad=20
no effect.

Whenever I run this line, I get an error (error 1064 You have an error in=
your=20
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=
=20
start of the quoted section of a line.

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

What am I doing wrong?

Thanks!

Hal

------------------------------------------------------------ ---------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail msql-mysql-modules-thread2086@lists.mysql.com

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-mysql-modules-unsubscribe@lists.mysql.com instead.

Re: Cannot use SELECT in INSERT statement (book says I can!)

> 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!
>
> Hal
>
> ------------------------------------------------------------ ---------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail msql-mysql-modules-thread2086@lists.mysql.com
>
> 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-mysql-modules-unsubscribe@lists.mysql.com instead.
>



------------------------------------------------------------ ---------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail msql-mysql-modules-thread2088@lists.mysql.com

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-mysql-modules-unsubscribe@lists.mysql.com instead.

Re: Cannot use SELECT in INSERT statement (book says I can!)

> 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!
>
> Hal
>
> ------------------------------------------------------------ ---------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail msql-mysql-modules-thread2086@lists.mysql.com
>
> 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-mysql-modules-unsubscribe@lists.mysql.com instead.
>



------------------------------------------------------------ ---------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail msql-mysql-modules-thread2088@lists.mysql.com

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-mysql-modules-unsubscribe@lists.mysql.com instead.

Re: Cannot use SELECT in INSERT statement (book says I can!)

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.

--
Dodger

----- Original Message -----
From: "Ulrich Borchers"
To: ;
Sent: Tuesday, March 18, 2003 7:51 AM
Subject: Re: Cannot use SELECT in INSERT statement (book says I can!)


> > 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!
> >
> > Hal
> >
> > ------------------------------------------------------------ ---------
> > Please check "http://www.mysql.com/Manual_chapter/manual_toc.html"
before
> > posting. To request this thread, e-mail
msql-mysql-modules-thread2086@lists.mysql.com
> >
> > 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-mysql-modules-unsubscribe@lists.mysql.com instead.
> >
>
>
>
> ------------------------------------------------------------ ---------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail
msql-mysql-modules-thread2088@lists.mysql.com
>
> 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-mysql-modules-unsubscribe@lists.mysql.com instead.
>
>


------------------------------------------------------------ ---------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail msql-mysql-modules-thread2089@lists.mysql.com

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-mysql-modules-unsubscribe@lists.mysql.com instead.

Re: Cannot use SELECT in INSERT statement (book says I can!)

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.

--
Dodger

----- Original Message -----
From: "Ulrich Borchers"
To: ;
Sent: Tuesday, March 18, 2003 7:51 AM
Subject: Re: Cannot use SELECT in INSERT statement (book says I can!)


> > 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!
> >
> > Hal
> >
> > ------------------------------------------------------------ ---------
> > Please check "http://www.mysql.com/Manual_chapter/manual_toc.html"
before
> > posting. To request this thread, e-mail
msql-mysql-modules-thread2086@lists.mysql.com
> >
> > 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-mysql-modules-unsubscribe@lists.mysql.com instead.
> >
>
>
>
> ------------------------------------------------------------ ---------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail
msql-mysql-modules-thread2088@lists.mysql.com
>
> 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-mysql-modules-unsubscribe@lists.mysql.com instead.
>
>


------------------------------------------------------------ ---------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail msql-mysql-modules-thread2089@lists.mysql.com

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-mysql-modules-unsubscribe@lists.mysql.com instead.

Re: Cannot use SELECT in INSERT statement (book says I can!)

Maybe MySQL has non-standardised syntax but I have looked PostgreSQL syntax
and is the same:

INSERT INTO table [ ( column [, ...] ) ]
{ DEFAULT VALUES | VALUES ( expression [, ...] ) | SELECT query }

If you put a SELECT query, you must not put VALUES.

----- Original Message -----
From: "Dodger"
To: ; ;

Sent: Tuesday, March 18, 2003 10:37 AM
Subject: Re: Cannot use SELECT in INSERT statement (book says I can!)


> 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.
>
> --
> Dodger
>
> ----- Original Message -----
> From: "Ulrich Borchers"
> To: ;
> Sent: Tuesday, March 18, 2003 7:51 AM
> Subject: Re: Cannot use SELECT in INSERT statement (book says I can!)
>
>
> > > 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!
> > >
> > > Hal
> > >
> > > ------------------------------------------------------------ ---------
> > > Please check "http://www.mysql.com/Manual_chapter/manual_toc.html"
> before
> > > posting. To request this thread, e-mail
> msql-mysql-modules-thread2086@lists.mysql.com
> > >
> > > 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-mysql-modules-unsubscribe@lists.mysql.com instead.
> > >
> >
> >
> >
> > ------------------------------------------------------------ ---------
> > Please check "http://www.mysql.com/Manual_chapter/manual_toc.html"
before
> > posting. To request this thread, e-mail
> msql-mysql-modules-thread2088@lists.mysql.com
> >
> > 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-mysql-modules-unsubscribe@lists.mysql.com instead.
> >
> >
>
>
> ------------------------------------------------------------ ---------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail
msql-mysql-modules-thread2089@lists.mysql.com
>
> 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-mysql-modules-unsubscribe@lists.mysql.com instead.
>


------------------------------------------------------------ ---------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail msql-mysql-modules-thread2090@lists.mysql.com

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-mysql-modules-unsubscribe@lists.mysql.com instead.

Re: Cannot use SELECT in INSERT statement (book says I can!)

Maybe MySQL has non-standardised syntax but I have looked PostgreSQL syntax
and is the same:

INSERT INTO table [ ( column [, ...] ) ]
{ DEFAULT VALUES | VALUES ( expression [, ...] ) | SELECT query }

If you put a SELECT query, you must not put VALUES.

----- Original Message -----
From: "Dodger"
To: ; ;

Sent: Tuesday, March 18, 2003 10:37 AM
Subject: Re: Cannot use SELECT in INSERT statement (book says I can!)


> 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.
>
> --
> Dodger
>
> ----- Original Message -----
> From: "Ulrich Borchers"
> To: ;
> Sent: Tuesday, March 18, 2003 7:51 AM
> Subject: Re: Cannot use SELECT in INSERT statement (book says I can!)
>
>
> > > 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!
> > >
> > > Hal
> > >
> > > ------------------------------------------------------------ ---------
> > > Please check "http://www.mysql.com/Manual_chapter/manual_toc.html"
> before
> > > posting. To request this thread, e-mail
> msql-mysql-modules-thread2086@lists.mysql.com
> > >
> > > 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-mysql-modules-unsubscribe@lists.mysql.com instead.
> > >
> >
> >
> >
> > ------------------------------------------------------------ ---------
> > Please check "http://www.mysql.com/Manual_chapter/manual_toc.html"
before
> > posting. To request this thread, e-mail
> msql-mysql-modules-thread2088@lists.mysql.com
> >
> > 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-mysql-modules-unsubscribe@lists.mysql.com instead.
> >
> >
>
>
> ------------------------------------------------------------ ---------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail
msql-mysql-modules-thread2089@lists.mysql.com
>
> 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-mysql-modules-unsubscribe@lists.mysql.com instead.
>


------------------------------------------------------------ ---------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail msql-mysql-modules-thread2090@lists.mysql.com

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-mysql-modules-unsubscribe@lists.mysql.com instead.

Re: Cannot use SELECT in INSERT statement (book says I can!)

On Tue, 18 Mar 2003, 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;


You need your VALUES clause in parentheses, and the number of your values
must match the number of your insert fields.

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

but of course you are selecting all records in xfer2 ...

Hope this helps,

Nick






>
> (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!
>
> Hal
>
> ------------------------------------------------------------ ---------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail msql-mysql-modules-thread2086@lists.mysql.com
>
> 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-mysql-modules-unsubscribe@lists.mysql.com instead.
>
>


- nick

--

~~~~~~~~~~~~~~~~~~~~
Nick Tonkin {|8^)>


------------------------------------------------------------ ---------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail msql-mysql-modules-thread2093@lists.mysql.com

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-mysql-modules-unsubscribe@lists.mysql.com instead.

Re: Cannot use SELECT in INSERT statement (book says I can!)

On Tue, 18 Mar 2003, 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;


You need your VALUES clause in parentheses, and the number of your values
must match the number of your insert fields.

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

but of course you are selecting all records in xfer2 ...

Hope this helps,

Nick






>
> (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!
>
> Hal
>
> ------------------------------------------------------------ ---------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail msql-mysql-modules-thread2086@lists.mysql.com
>
> 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-mysql-modules-unsubscribe@lists.mysql.com instead.
>
>


- nick

--

~~~~~~~~~~~~~~~~~~~~
Nick Tonkin {|8^)>


------------------------------------------------------------ ---------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail msql-mysql-modules-thread2093@lists.mysql.com

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-mysql-modules-unsubscribe@lists.mysql.com instead.

RE: Cannot use SELECT in INSERT statement (book says I can!)

Dodger,

I'm not sure what "standard" you are referring to, but according to ANSI
X3.135-1986 for SQL:


8.7

Function

Create new rows in a table.

Format

::=
INSERT INTO