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'

Answer 2

it is not necessary to be straight copy. Simply make the result of the select statement matches the column of your target table.

e.g.

insert into NEW_TAB(user_name, order_id) 
select u.user_name, o.order_id 
from ORDER o
inner join USER u
  on o.user_id = u.user_id

Answer 3

Assuming that your tables are structured something like:

users:
    user_id           integer
    user_name         varchar(50)
    user_other_data   varchar(999)
    primary key       (user_id)
    unique            (user_name)
other_table:
    user_value        varchar(999)
    user_id           integer foreign key (users.user_id)

What you want is the ability to insert an entry into other_table given only the value and the user name.

It's important that you do this in an atomic way to ensure the user_id/user_name mapping isn't changed underneath you. In other words, you can't do:

get user_id from users based on user_name.
insert value and user_id into other_table.

since the user_id or user_name may change between those two steps above (the user_id probably wouldn't change for the physical row, but it's certainly possible that the table could change from:

user_id  user_name
-------  ---------
1        bob
2        george

to:

user_id  user_name
-------  ---------
1        george
2        bob

So, to do it atomically, it has to be in a single SQL statement (or part of a transaction but you can avoid a transaction in this case):

insert into other_table (user_value, user_id)
    select :value, user_id from users where user_name = :name;

For your specific case (added after my original answer), you're looking at:

insert into AvatarTable (UserID,AvatarURL)
    select UserID, :localAvatarURL
    from UserTable
    where UserName = :localUserName;