At 12:54 -0700 7/4/04, bruce wrote:
>hi...
>
>i have two hypothetical tables
>create table owner (
> -> name char(20) ,
> -> ownerid int(10) auto_increment primary key);
>
>create table dog (
> -> name char(20) ,
> -> ownerid int(10),
> -> dogid int(10) auto_increment primary key);
>
>i'm curious as to how i'd go about inserting a name and the id of the owner,
>in table "dog", in a single sql statement.
>
>something like this psuedo sql..
> insert table (name, ownerid) values ($name, $ownerid)
> where owner.owner = owner
This won't work, because there is no guarantee that the name of your
owner is unique.
Some techniques that might be useful in this situation can be found
here:
http://www.onlamp.com/pub/a/onlamp/excerpt/mysqlckbk/index2.html
>
>in other words, an app would supply the values for the "dog name", and the
>"owner". the sql would be able to derive the "ownerid" for the "owner" from
>the owner table, and then be able to insert the "ownerid", and "dogname"
>into the dog table...
>
>searching through google gets me to being able to write a php/perl script
>where i can do this using multiple sql statements.. but i'm trying to see
>how to do it in a single statement...
You can't, for the reason noted above.
If you happen to have a unique index on the owner.name column, then
what you might want to try is the INSERT INTO ... SELECT FROM form
of INSERT.
http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html
>
>any comments/criticisms would be helpful...
>
>thanks...
>
>-bruce
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
