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','email@example.com','078953 33555','Someone Somewhere','london',hair.id
WHERE hid.colur = 'red'
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.