List:General Discussion« Previous MessageNext Message »
From:Quentin Bennett Date:July 4 2004 10:40pm
Subject:RE: mysql sql question
View as plain text  
Hi,

You've already specified some values, so you can't then add a 'select' clause as well.

Try

mysql> insert into dept (name,collegeid)
    -> select 'tom', id from test where name="sammy";

HTH

Quentin

-----Original Message-----
From: bruce [mailto:bedouglas@stripped]
Sent: Monday, 5 July 2004 10:41 a.m.
To: 'Emmett Bishop'; mysql@stripped
Subject: RE: mysql sql question


i created the following as a simple test...

mysql> describe test;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| name  | char(20) | YES  | MUL | NULL    |                |
| id    | int(10)  |      | PRI | NULL    | auto_increment |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> describe dept;
+-----------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra          |
+-----------+----------+------+-----+---------+----------------+
| name      | char(20) | YES  | MUL | NULL    |                |
| collegeid | int(10)  | YES  |     | NULL    |                |
| nameid    | int(10)  |      | PRI | NULL    | auto_increment |
+-----------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> select * from test;
+--------+----+
| name   | id |
+--------+----+
| sa     |  1 |
| be     |  2 |
| sam1ss |  3 |
| sammy  |  4 |
+--------+----+
4 rows in set (0.00 sec)

mysql> select * from dept;
Empty set (0.00 sec)


i tried...
mysql> insert into dept (name,collegeid) values ('tom',test.id)
    -> select id from test where name="sammy";
and got the following error...

ERROR 1064: You have an error in your SQL syntax.  Check the manual that
corresponds to your MySQL server version for the right syntax to use near
'select id from test where name="sammy"' at line 2

i've tried a number of different iterations.. any ideas as to why this
doesn't seem to work...????

thanks....

-bruce

ps. also, if i do/can manage to get this to work, shouldn't it be possible
to extend the basic approach to multiple tables, using FROM tbl1, tbl2,
tbl3..

-----------------------------


-----Original Message-----
From: bruce [mailto:bedouglas@stripped]
Sent: Sunday, July 04, 2004 3:26 PM
To: 'Emmett Bishop'; mysql@stripped
Subject: RE: mysql sql question


what you provided would almost do it... but i want to insert into the "dog
table" the name that i submit...

i'd like to do something like....

if i submit dogname, ownername

insert into dog (name, ownerid) values ($dogname, owner.id)
select ownerid from owner where owner.name = $ownername;

-bruce



-----Original Message-----
From: Emmett Bishop [mailto:dyne_erg@stripped]
Sent: Sunday, July 04, 2004 12:59 PM
To: bedouglas@stripped
Subject: Re: mysql sql question


Bruce,

what you want it the insert into ... select statement.

It's like this:

insert into dog (name, ownerid)
select name, ownerid from owner where ownerid = 8;

Does that sound like what you need?

-- Tripp



--- bruce <bedouglas@stripped> 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
>
> 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...
>
> any comments/criticisms would be helpful...
>
> thanks...
>
> -bruce
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
>
http://lists.mysql.com/mysql?unsub=1
>
>





__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=1


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

The information contained in this email is privileged and confidential and
intended for the addressee only. If you are not the intended recipient, you
are asked to respect that confidentiality and not disclose, copy or make use
of its contents. If received in error you are asked to destroy this email
and contact the sender immediately. Your assistance is appreciated.
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