Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012
    Posts
    2

    Unanswered: INSERT and subquery - cant figure this out

    I have found many examples and information around this but still cant get this to work. What I am trying to do is insert into a table, but query two other tables to get the foreign keys so I need to use a subquery.

    I have 3 tables: user, hair and city. Hair contains different colours and city are different cities. The below example i am trying to use does not try and insert city (its been added manually for now but will need to be another subquery) and have tried to insert hair colour.

    This is what I have so far:

    INSERT INTO user (lid,random,email,phone,name,city,hair)
    SELECT '','19293642446','someone@whereareyou.com','078953 33555','Someone Somewhere','london',hair.id
    FROM hair
    WHERE hid.colur = 'red'

    There is no error just doesnt insert a new row.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by tbobker View Post
    There is no error ...
    sorry, there is so

    where are you running this query from? php?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    If your SELECT statement above returns a result, then you must have an error during the insert statement. If the SELECT statement fails (due to colur = 'red' not existing) then you will need to look at adding this in before attempting this insert.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

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