Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2012
    Posts
    91

    Unanswered: INSERT ... SELECT with subquery

    Following the advice of experienced developers I used manula the MySQL on INSERT ... SELECT. Well, what i found there?
    quotation: With INSERT ... SELECT, you can quickly insert many rows into a table from one or many tables.
    Hopefully in what I read I started to work.
    Code:
    INSERT INTO TABLE (ID,ID1) VALUES ('0', (SELECT ID FROM TABLE1 WHERE ID=1))
    What I saw?
    Error: Subquery returns more than one row
    Bingo!
    So what I do not understand?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by duf View Post
    So what I do not understand?
    you appear to have overlooked this

    an example of the correct syntax is here

    tonkuma even explained the syntax to you here
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2012
    Posts
    91
    Answer is:
    Code:
    INSERT INTO TABLE (ID,ID1) SELECT ID ,1 FROM TABLE1 WHERE ID=1
    And what is difference between that and
    Code:
    INSERT INTO TABLE (ID,ID1) VALUES ('0', (SELECT ID FROM TABLE1 WHERE ID=1))
    In the first example SELECT return more than one row and in the second one only one row?

    But how should look like query where first subquery return one row and the second one return more than one?
    Code:
    INSERT INTO TABLE (ID,ID1) SELECT ID FROM TABLE2, SELECT ID FROM TABLE1 WHERE ID=1
    Code:
    INSERT INTO TABLE (ID,ID1) one row, more than one row
    Last edited by duf; 07-11-12 at 05:30.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

  5. #5
    Join Date
    Jan 2012
    Posts
    91
    I am glad that you are in the good mood.
    Code:
    INSERT INTO TABLE (ID,ID1) SELECT ID, (SELECT ID FROM TABLE1) FROM TABLE2 WHERE ID=1
    Last edited by duf; 07-11-12 at 05:54.

Posting Permissions

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