Simple SQL question, need help.

feech wrote:

I'm pretty much an idiot, but I can't figure out how to do this:

I have a results DB with two fields: PlayerID and Position. I also
have a players DB with two fields: ID and Name.

How do I insert a record into results with PlayerID selected from the
players DB and Position explicitly specified by me.

Something like:

INSERT INTO results (PlayerID, Position) values (SELECT players.id
from players WHERE name = 'Chuck', 4 );

That statement is trying to insert the player's ID (I only have his
name when inserting) and his position ( 4 in this case ), but it
doesn't work.

Totally frustrated,
feech

You can define the position as a constant in your resultset from the
table player:

insert into results select player_id, 4 as position from player where
name = 'Chuck';

HTH,

Nils Zonneveld

fyzfeech@yahoo.com (feech) writes:

INSERT INTO results (PlayerID, Position) values (SELECT players.id
from players WHERE name = 'Chuck', 4 );

You need to put parentheses around the sub-select used as an
expression:

INSERT INTO results (PlayerID, Position) values ((SELECT players.id
from players WHERE name = 'Chuck'), 4 );

A more conventional way to get the same result is

INSERT INTO results (PlayerID, Position)
SELECT id, 4 FROM players WHERE name = 'Chuck';

regards, tom lane