Re: INSERT WITH SELECT help

Jurgen Defurne wrote:
>
> 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

I got help from one of our DBA's today here is what I was missing. This
in not
in Bruce's Book.

INSERT INTO address (per_id,street_num,city,state,zip)
SELECT per_id,'$3','$4','$5','$6' FROM from person
WHERE last = '$1'
AND first = '$2';

$1-6 will be supplied by user input from Zope, I just wanted to isolate
the
per_id from person during and Insert so that end users would not need to
know
it was there. I know I might have a problem with getting more then one
return for
just first and last, I might add more WHERE statements in there. I am
just happy to get moving on with my little project.

Thanks for the help
Richad