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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
