List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:July 4 2004 7:59pm
Subject:Re: mysql sql question
View as plain text  
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
Thread
date and time functions.Hari Yellina4 Jul
Re: date and time functions.Eric Bergen4 Jul
  • mysql sql questionbruce4 Jul
    • Re: mysql sql questionPaul DuBois4 Jul
      • RE: mysql sql questionbruce5 Jul
    • Re: mysql sql questionPeter Brawley6 Jul
RE: mysql sql questionbruce5 Jul
  • RE: mysql sql questionbruce5 Jul
RE: mysql sql questionQuentin Bennett5 Jul
  • RE: mysql sql questionbruce5 Jul
RE: mysql sql questionQuentin Bennett5 Jul
  • RE: mysql sql questionbruce5 Jul
    • mysql sql files...bruce5 Jul
      • RE: mysql sql files...bruce5 Jul
      • Re: mysql sql files...Paul DuBois5 Jul
    • Re: mysql sql questionJohn Hicks5 Jul
Re: mysql sql questionSGreen7 Jul