Re: INSERT WITH SELECT help

Richard Smith wrote:

> I am new to SQL so bare with me here.
>
> I have set up a contact database. The PRIMANY KEY is person.per_id
> All the other tables REFERENCE the person.per_id key. Now I want to be
> able
> to INSERT INTO the address table based on person.per_id by name without
> having to know the value of person.per_id. Something like, I know this
> does not work
> but you will get the idea of what I need.
>
> INSERT INTO address
> (per_id,street,city,state,zip)
> VALUES ('('SELECT per_id FROM person WHERE first ='somename')','200 some
> street',
> 'Tampa','FL','33654');
>
> Can somthing like this be done ? Any help would be great.

What you desire is very errorprone. Unless you have a program that does the

things you want, user input is not reliable enough to use as the subselect
you
want here.

Basically, what you are doing here is to check the input 'somename' against

the database 'person'. Wouldn't it be better then, if you directly check
your
user input against your database, in which case you would have a valid
'per_id'
or else you have to display a user error ?

Should it be an automated system, then what you need is an expression.
Since
this may be a function, you can embed your subquery into a function, and
rewrite the VALUES clause as :
VALUES(select_function(), ....)

Good luck.

Jurgen Defurne
defurnj(at)glo(dot)be