Hi,
The insert says 'insert data in to two columns, name and collegeid'.
The select says "get two columns, 'tom' and id" - 'tom' is a fixed value, the same for
each row, and id is taken from the test table.
If you want the name from test, then use
mysql> insert into dept (name,collegeid)
-> select name, id from test where name="sammy";
-----Original Message-----
From: bruce [mailto:[EMAIL PROTECTED]
Sent: Monday, 5 July 2004 10:50 a.m.
To: Quentin Bennett
Subject: RE: mysql sql question
i can't see how this would work at all...
unless you're saying the select will return "tom" and stuff that into the dept table
as the "name" value.....
and where/how would the "collegeid" of the insert be derived from..
-bruce
-----Original Message-----
From: Quentin Bennett [mailto:[EMAIL PROTECTED]
Sent: Sunday, July 04, 2004 3:41 PM
To: [EMAIL PROTECTED]; Emmett Bishop; [EMAIL PROTECTED]
Subject: RE: mysql sql question
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:[EMAIL PROTECTED]
Sent: Monday, 5 July 2004 10:41 a.m.
To: 'Emmett Bishop'; [EMAIL PROTECTED]
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:[EMAIL PROTECTED]
Sent: Sunday, July 04, 2004 3:26 PM
To: 'Emmett Bishop'; [EMAIL PROTECTED]
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:[EMAIL PROTECTED]
Sent: Sunday, July 04, 2004 12:59 PM
To: [EMAIL PROTECTED]
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 <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED]
>
>
__________________________________
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/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
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.
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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
