Professional Web Applications Themes

INSERT .. SELECT syntax - MySQL

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

  1. #1

    Default INSERT .. SELECT syntax

    -----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: net
    Unix Systems Administrator, | com.au
    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 Guest

  2. #2

    Default Re: INSERT .. SELECT syntax

    A Guy Called Tyketto 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'





    Paul Guest

  3. #3

    Default Re: INSERT .. SELECT syntax

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


    Paul Lautman <com> wrote: 

    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: net
    Unix Systems Administrator, | com.au
    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 Guest

  4. #4

    Default Re: INSERT .. SELECT syntax

    A Guy Called Tyketto wrote: 
    >
    > 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.[/ref]

    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?



    Paul Guest

  5. #5

    Default Re: INSERT .. SELECT syntax

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

    Paul Lautman <com> wrote: 

    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: net
    Unix Systems Administrator, | com.au
    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 Guest

  6. #6

    Default Re: INSERT .. SELECT syntax


    A Guy Called Tyketto wrote:
     
    >
    > 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: net
    > Unix Systems Administrator, | com.au
    > 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-----[/ref]

    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.
    net
    ==================
    Jerry Guest

  7. #7

    Default Re: INSERT .. SELECT syntax

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

    Jerry Stuckle <net> wrote: 
    >
    > 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.[/ref]

    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: net
    Unix Systems Administrator, | com.au
    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 Guest

  8. #8

    Default Re: INSERT .. SELECT syntax

    A Guy Called Tyketto wrote: 
    >>
    >> 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.[/ref]
    >
    > 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.[/ref]

    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 Guest

  9. #9

    Default Re: INSERT .. SELECT syntax

    Paul Lautman wrote: 
    >>
    >> 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.[/ref]
    >
    > 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.[/ref]

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


    Paul Guest

  10. #10

    Default Re: INSERT .. SELECT syntax

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

    Paul Lautman <com> wrote: 

    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: net
    Unix Systems Administrator, | com.au
    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-----
    A Guest

Similar Threads

  1. Error in Insert into syntax
    By Hydrowizard in forum Coldfusion Flash Integration
    Replies: 0
    Last Post: October 5th, 01:44 PM
  2. What's faster - loop for insert or insert...select.
    By mr. modus in forum Coldfusion Database Access
    Replies: 2
    Last Post: December 10th, 01:57 AM
  3. INSERT MAX(ID) Syntax problem
    By Targa in forum ASP Database
    Replies: 2
    Last Post: May 1st, 08:01 PM
  4. SELECT: Syntax error. Please help
    By sam in forum PHP Development
    Replies: 14
    Last Post: September 29th, 10:32 PM
  5. Help with Select Syntax
    By Chaster in forum Microsoft Access
    Replies: 1
    Last Post: July 22nd, 10:24 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139