-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


I'll try to keep this short and sweet.

I'm having an issue with the insert ... select syntax. I know I
can do the following:

insert into db1.table(column) select column from
db2.table.column2 where db2.table.column1 = 'x';

which works fine. But what I also need to do is add additional
values to the INSERT portion of the statement. These values are not a
part of the select portion of the statement. I've tried the following,
to no avail:

insert into db.table1(a,b,c,d) values ('w','x', select j
from db2.table.column2 where db2.table.column1 = 'k','z');

I just want to keep it all in one insert .. select statement
instead of doing one insert .. select statement and one insert
statement for the same line of data. Any ideas?

BL.
- - --
Brad Littlejohn | Email: EMAIL REMOVED
Unix Systems Administrator, | EMAIL REMOVED
Web + NewsMaster, BOFH.. Smeghead! | http://www.wizard.com/~tyketto
PGP: 1024D/E319F0BF 6980 AAD6 7329 E9E6 D569 F620 C819 199A E319 F0BF
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFscSQyBkZmuMZ8L8RAgWGAJ4rh7+Ycxo2FJEVcCb0Q9 MWW0v2BACfY8x8
c+qoQacwTAD9OZ4mSkd9Gbo=
=+1fG
-----END PGP SIGNATURE-----

A Guy Called Tyketto wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> I'll try to keep this short and sweet.
>
> I'm having an issue with the insert ... select syntax. I know I
> can do the following:
>
> insert into db1.table(column) select column from
> db2.table.column2 where db2.table.column1 = 'x';
>
> which works fine. But what I also need to do is add additional
> values to the INSERT portion of the statement. These values are not a
> part of the select portion of the statement. I've tried the following,
> to no avail:
>
> insert into db.table1(a,b,c,d) values ('w','x', select j
> from db2.table.column2 where db2.table.column1 = 'k','z');
>
> I just want to keep it all in one insert .. select statement
> instead of doing one insert .. select statement and one insert
> statement for the same line of data. Any ideas?
>
> BL.

Include them in the SELECT statement thus:

INSERT INTO `db`.`table1` (`a`,`b`,`c`,`d`)
SELECT 'w', 'x', `j`, 'a' FROM `db2`.`table` WHERE db2`.`table`.`column1` =
'k'

To make this clearer, here it is again with the backticks left out:

INSERT INTO db.table1 (a,b,c,d)
SELECT 'w', 'x', j, 'a' FROM db2.table WHERE db2.table.column1 = 'k'

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Paul Lautman <EMAIL REMOVED> wrote:
>
> Include them in the SELECT statement thus:
>
> INSERT INTO `db`.`table1` (`a`,`b`,`c`,`d`)
> SELECT 'w', 'x', `j`, 'a' FROM `db2`.`table` WHERE db2`.`table`.`column1` =
> 'k'
>
> To make this clearer, here it is again with the backticks left out:
>
> INSERT INTO db.table1 (a,b,c,d)
> SELECT 'w', 'x', j, 'a' FROM db2.table WHERE db2.table.column1 = 'k'

This looks to work, thanks. But the problem is, is that a, b,
and d are user input, and won't be coming from the SELECT statement.
Only j will be coming from the select statement, where the others will
be input from a form. How would the statement go if values are used
along with the SELECT statement?

BL.
- - --
Brad Littlejohn | Email: EMAIL REMOVED
Unix Systems Administrator, | EMAIL REMOVED
Web + NewsMaster, BOFH.. Smeghead! | http://www.wizard.com/~tyketto
PGP: 1024D/E319F0BF 6980 AAD6 7329 E9E6 D569 F620 C819 199A E319 F0BF
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFsnmAyBkZmuMZ8L8RAnD0AKCE1H87B/77IKALGGepYhx0oTOT/gCfVMGM
wArsQKogk7G6+9vmE9ppoE8=
=RIPw
-----END PGP SIGNATURE-----

A Guy Called Tyketto wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> Paul Lautman <EMAIL REMOVED> wrote:
>>
>> Include them in the SELECT statement thus:
>>
>> INSERT INTO `db`.`table1` (`a`,`b`,`c`,`d`)
>> SELECT 'w', 'x', `j`, 'a' FROM `db2`.`table` WHERE
>> db2`.`table`.`column1` = 'k'
>>
>> To make this clearer, here it is again with the backticks left out:
>>
>> INSERT INTO db.table1 (a,b,c,d)
>> SELECT 'w', 'x', j, 'a' FROM db2.table WHERE db2.table.column1 = 'k'
>
> This looks to work, thanks. But the problem is, is that a, b,
> and d are user input, and won't be coming from the SELECT statement.
> Only j will be coming from the select statement, where the others will
> be input from a form. How would the statement go if values are used
> along with the SELECT statement?
>
> BL.

Your question is not clear? a, b, c and d are column names, whereas j is a
column value? w, x and a (the secon a) are values. Rather than using letters
(and repeated letters where one is both one thing and a different thing),
how about giving an actual example of what you are talking about?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Paul Lautman <EMAIL REMOVED> wrote:
>
> Your question is not clear? a, b, c and d are column names, whereas j is a
> column value? w, x and a (the secon a) are values. Rather than using letters
> (and repeated letters where one is both one thing and a different thing),
> how about giving an actual example of what you are talking about?

I was just thinking that. I'm working with weather METAR
reports, and how it purtains to certain airports. My feed for the METAR
reports comes from NOAA, into database weather, with the following fields:

icao(char) time(timestamp) metar(varchar) timestamp(timestamp)

I have table info database aceids set up to print the information
out to a page that has the weather for a certain list of airports, and also
has user-added information added to the database. It has the following:

icao(char) alpha(char) metar(varchar) (approach) notams(varchar)
timestamp(timestamp)

So the following works to get the icao code, METAR, and
timestamp fields into the info table:

insert into aceids.info(metar) select icao,metar from
weather.pw_metars where weather.pw_metars.icao = 'KLAS' and
weather.pw_metars.timestamp ='2007-01-20 00:56:00';

Works great. But the alpha, approach, and notams fields will
come from user input, so VALUES() will be needed. Whenever I use that
as well as a SELECT in the statement, I get syntax errors. Is it possible
to get all of those added to the database in one query?

BL.
- - --
Brad Littlejohn | Email: EMAIL REMOVED
Unix Systems Administrator, | EMAIL REMOVED
Web + NewsMaster, BOFH.. Smeghead! | http://www.wizard.com/~tyketto
PGP: 1024D/E319F0BF 6980 AAD6 7329 E9E6 D569 F620 C819 199A E319 F0BF

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFsqBAyBkZmuMZ8L8RAvffAKC9+/dJ1sXhwlDOIvnbvLzJVQYvEgCfci2c
2Poj1IpmezpSRdLQxkXd9jE=
=LPHK
-----END PGP SIGNATURE-----

A Guy Called Tyketto wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Paul Lautman <EMAIL REMOVED> wrote:
>> Your question is not clear? a, b, c and d are column names, whereas j is a
>> column value? w, x and a (the secon a) are values. Rather than using letters
>> (and repeated letters where one is both one thing and a different thing),
>> how about giving an actual example of what you are talking about?
>
> I was just thinking that. I'm working with weather METAR
> reports, and how it purtains to certain airports. My feed for the METAR
> reports comes from NOAA, into database weather, with the following fields:
>
> icao(char) time(timestamp) metar(varchar) timestamp(timestamp)
>
> I have table info database aceids set up to print the information
> out to a page that has the weather for a certain list of airports, and also
> has user-added information added to the database. It has the following:
>
> icao(char) alpha(char) metar(varchar) (approach) notams(varchar)
> timestamp(timestamp)
>
> So the following works to get the icao code, METAR, and
> timestamp fields into the info table:
>
> insert into aceids.info(metar) select icao,metar from
> weather.pw_metars where weather.pw_metars.icao = 'KLAS' and
> weather.pw_metars.timestamp ='2007-01-20 00:56:00';
>
> Works great. But the alpha, approach, and notams fields will
> come from user input, so VALUES() will be needed. Whenever I use that
> as well as a SELECT in the statement, I get syntax errors. Is it possible
> to get all of those added to the database in one query?
>
> BL.
> - - --
> Brad Littlejohn | Email: EMAIL REMOVED
> Unix Systems Administrator, | EMAIL REMOVED
> Web + NewsMaster, BOFH.. Smeghead! | http://www.wizard.com/~tyketto
> PGP: 1024D/E319F0BF 6980 AAD6 7329 E9E6 D569 F620 C819 199A E319 F0BF
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD8DBQFFsqBAyBkZmuMZ8L8RAvffAKC9+/dJ1sXhwlDOIvnbvLzJVQYvEgCfci2c
> 2Poj1IpmezpSRdLQxkXd9jE=
> =LPHK
> -----END PGP SIGNATURE-----

As Paul said - put them in your SELECT statement.

His example gives exact values but you can substitute variables from
your favorite language (which contain the correct values).

You're building the SELECT statement dynamically, so you can put
anything you want in there.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
EMAIL REMOVED
==================

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Jerry Stuckle <EMAIL REMOVED> wrote:
> A Guy Called Tyketto wrote:
>
>> So the following works to get the icao code, METAR, and
>> timestamp fields into the info table:
>>
>> insert into aceids.info(metar) select icao,metar from
>> weather.pw_metars where weather.pw_metars.icao = 'KLAS' and
>> weather.pw_metars.timestamp ='2007-01-20 00:56:00';
>>
>> Works great. But the alpha, approach, and notams fields will
>> come from user input, so VALUES() will be needed. Whenever I use that
>> as well as a SELECT in the statement, I get syntax errors. Is it possible
>> to get all of those added to the database in one query?
>
> As Paul said - put them in your SELECT statement.
>
> His example gives exact values but you can substitute variables from
> your favorite language (which contain the correct values).
>
> You're building the SELECT statement dynamically, so you can put
> anything you want in there.

But it isn't working. That's my whole point. Here's my
statement:

INSERT into aceids.info(icao,alpha,metar,approach,notams) select icao
from phpweather.pw_metars where phpweather.pw_metars.icao = 'KLAS',
values ('G'), select metar from phpweather.pw_metars where
phpweather.pw_metars.icao = 'KLAS', values ('25L 19L 19R/25R 19R 19L',
'19L north 1850ft unavailable');

Which gets me the following:

ERROR 1064 (42000): 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 ' values ('G'), select metar from
phpweather.pw_metars where phpweather.pw_metars' at line 1

The data for alpha, approach, and notams are going to come from
user input data from a web page using php. According to the docs, those
would need to be in the values section of the query.

I must be missing something, but I'm not sure what..

BL.
- --
Brad Littlejohn | Email: EMAIL REMOVED
Unix Systems Administrator, | EMAIL REMOVED
Web + NewsMaster, BOFH.. Smeghead! | http://www.wizard.com/~tyketto
PGP: 1024D/E319F0BF 6980 AAD6 7329 E9E6 D569 F620 C819 199A E319 F0BF

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFswp+yBkZmuMZ8L8RAjIoAJ935k9y+FW2H2T7cUPwSF LoTSAZXwCeOL+u
/F50Dgdgyu3sq5PHJMHiMTQ=
=XuI/
-----END PGP SIGNATURE-----

A Guy Called Tyketto wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Jerry Stuckle <EMAIL REMOVED> wrote:
>> A Guy Called Tyketto wrote:
>>
>>> So the following works to get the icao code, METAR, and
>>> timestamp fields into the info table:
>>>
>>> insert into aceids.info(metar) select icao,metar from
>>> weather.pw_metars where weather.pw_metars.icao = 'KLAS' and
>>> weather.pw_metars.timestamp ='2007-01-20 00:56:00';
>>>
>>> Works great. But the alpha, approach, and notams fields will
>>> come from user input, so VALUES() will be needed. Whenever I use
>>> that
>>> as well as a SELECT in the statement, I get syntax errors. Is it
>>> possible to get all of those added to the database in one query?
>>
>> As Paul said - put them in your SELECT statement.
>>
>> His example gives exact values but you can substitute variables from
>> your favorite language (which contain the correct values).
>>
>> You're building the SELECT statement dynamically, so you can put
>> anything you want in there.
>
> But it isn't working. That's my whole point. Here's my
> statement:
>
> INSERT into aceids.info(icao,alpha,metar,approach,notams) select icao
> from phpweather.pw_metars where phpweather.pw_metars.icao = 'KLAS',
> values ('G'), select metar from phpweather.pw_metars where
> phpweather.pw_metars.icao = 'KLAS', values ('25L 19L 19R/25R 19R 19L',
> '19L north 1850ft unavailable');
>
> Which gets me the following:
>
> ERROR 1064 (42000): 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 ' values ('G'), select metar from
> phpweather.pw_metars where phpweather.pw_metars' at line 1
>
> The data for alpha, approach, and notams are going to come from
> user input data from a web page using php. According to the docs,
> those would need to be in the values section of the query.
>
> I must be missing something, but I'm not sure what..
>
> BL.

The query that you have shown looks nothing like the example that I showed.
My query had only 1 VALUES keyword, yours has 2.
You need to use a query that looks like the one that I suggested.

Paul Lautman wrote:
> A Guy Called Tyketto wrote:
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
>> Jerry Stuckle <EMAIL REMOVED> wrote:
>>> A Guy Called Tyketto wrote:
>>>
>>>> So the following works to get the icao code, METAR, and
>>>> timestamp fields into the info table:
>>>>
>>>> insert into aceids.info(metar) select icao,metar from
>>>> weather.pw_metars where weather.pw_metars.icao = 'KLAS' and
>>>> weather.pw_metars.timestamp ='2007-01-20 00:56:00';
>>>>
>>>> Works great. But the alpha, approach, and notams fields will
>>>> come from user input, so VALUES() will be needed. Whenever I use
>>>> that
>>>> as well as a SELECT in the statement, I get syntax errors. Is it
>>>> possible to get all of those added to the database in one query?
>>>
>>> As Paul said - put them in your SELECT statement.
>>>
>>> His example gives exact values but you can substitute variables from
>>> your favorite language (which contain the correct values).
>>>
>>> You're building the SELECT statement dynamically, so you can put
>>> anything you want in there.
>>
>> But it isn't working. That's my whole point. Here's my
>> statement:
>>
>> INSERT into aceids.info(icao,alpha,metar,approach,notams) select icao
>> from phpweather.pw_metars where phpweather.pw_metars.icao = 'KLAS',
>> values ('G'), select metar from phpweather.pw_metars where
>> phpweather.pw_metars.icao = 'KLAS', values ('25L 19L 19R/25R 19R
>> 19L', '19L north 1850ft unavailable');
>>
>> Which gets me the following:
>>
>> ERROR 1064 (42000): 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 ' values ('G'), select metar from
>> phpweather.pw_metars where phpweather.pw_metars' at line 1
>>
>> The data for alpha, approach, and notams are going to come from
>> user input data from a web page using php. According to the docs,
>> those would need to be in the values section of the query.
>>
>> I must be missing something, but I'm not sure what..
>>
>> BL.
>
> The query that you have shown looks nothing like the example that I
> showed. My query had only 1 VALUES keyword, yours has 2.
> You need to use a query that looks like the one that I suggested.

What am I talking about!!! My query had NO VALUES keyword. It explained how
to insert your own values into the SELECT statement!

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Paul Lautman <EMAIL REMOVED> wrote:
>
> What am I talking about!!! My query had NO VALUES keyword. It explained how
> to insert your own values into the SELECT statement!

I get it now! The VALUES portion isn't needed, as the variables
are relative to the SELECT portion of the statement, not the INSERT
portion. I knew I was missing something there. I just tried it, and it
works perfectly.

Thanks!!

BL.
- --
Brad Littlejohn | Email: EMAIL REMOVED
Unix Systems Administrator, | EMAIL REMOVED
Web + NewsMaster, BOFH.. Smeghead! | http://www.wizard.com/~tyketto
PGP: 1024D/E319F0BF 6980 AAD6 7329 E9E6 D569 F620 C819 199A E319 F0BF

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFtGIKyBkZmuMZ8L8RArNZAJ95iMydCvrahCycM7qz4K +7+hNTugCbB8KB
YLdCj+e4isZ42Cm/4GxsC+Q=
=Qni1
-----END PGP SIGNATURE-----