Обсуждение: INSERT WITH SELECT help

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.

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@glo.be

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@glo.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

ozric wrote:

> 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

Your idea is good, normally you do not want to bother your user with unique
key values. However, have you thought about the fact that more than one
person could have the same first and last names ? Do not confuse the
improbable with the impossible. When names are entered, you should check
how much results you have and probably show another screen on which
the user can select the right person.

Jurgen Defurne
defurnj@glo.be

--snip--

Yes, I like the Idea of picking from a list if you get a return > 1.

off topic:

    The main point of this whole little project was to design a SQL
database and make a Web interface to it.  One of my  main goals
was to learn the how and why's of SQL.  My Zope skill are sub par
also, so a good bushing up on dtml and form design was number two.
Of course FUN was number three.  I must say I have meet my goals,
and I now see how far I have to go as A DBA or a ZOPIST.  So much
of what I have learn has really shown me that, anything can be done
with enough work, fore thought and of course help from others.

Richard