List:General Discussion« Previous MessageNext Message »
From:bruce Date:July 4 2004 10:17pm
Subject:RE: mysql sql question
View as plain text  
my bad...

should have mentioned that both the owner and dog name are unique, but one
owner can have multiple dogs...

so just how does the "insert ... select" work..????

it looks like it could actually do what i need, but i can't seem to get it
working correctly..

-bruce


-----Original Message-----
From: Paul DuBois [mailto:paul@stripped]
Sent: Sunday, July 04, 2004 12:59 PM
To: bedouglas@stripped; mysql@stripped
Subject: Re: mysql sql question


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