mysql - SQL INSERT from SELECT

Question

Lets say in my code I have the users name, and a value. Now I need to save that in the db but I need to first get the ID corresponding to that users name because this table links with a pk/fk to the users table. How do I do it? I know you can do a INSERT (blah) SELECT etc to do it but that looks like a straight copy, i need to insert the value with the fk column as the result from a SELECT.

User Table: [UserID(pk), UserName]

Avatar Table: [UserID(fk), AvatarURL]

I need to INSERT INTO AvatarTable(UserID, AvatarURL) VALUES (*id of user where UserName = 'theirname'*, 'http://www.blah.com')

Thanks

Answer 1

You might be looking for this?:

insert into myDestTable (userid, name, value, othercolumns)
select us.userid, us.name,'myvaluefromcode', othercolumns
from users us 
where us.name = 'mynamefromcode'