Harrison, you were right on the money. The statement you showed me
worked perfectly to do exactly what I wanted. I thought the only use of
the INSERT...SELECT statement was to duplicate subsets of tables in new
tables. This was a whole new use of it for me.
Thank you so much for your help.
Below, I've pasted in the statements I used to test this out, for
anyone following this thread in the future.
Thanks, again.
-Kevin Zembower
>>> "Harrison C. Fisk" <hcfisk@stripped> 05/09/02 11:29AM >>>
What you want is an INSERT...SELECT statement.
It would look something like the following:
INSERT INTO condmeth (methid, condid, recommendation) SELECT
method.methodid, condition.conditionid, 'Recommendation goes here'
from
method, condition WHERE method.sname='COC' AND condition.sname='Age';
That would almost work, although you might need some sort of join
clause
most likely describing where the two tables are to be joined, unless
you
want a cartesian product.
Here is the manual link for more information:
http://www.mysql.com/doc/I/N/INSERT_SELECT.html
Harrison
=====================================================
mysql> create table cond (condid int(11) NOT NULL auto_increment, cond
varchar(255) default NULL, sname varchar(255) default NULL, PRIMARY KEY
(condid));
Query OK, 0 rows affected (0.03 sec)
mysql> create table meth (methid int(11) NOT NULL auto_increment, meth
varchar(255) default NULL, sname varchar(255) default NULL, PRIMARY KEY
(methid));
Query OK, 0 rows affected (0.00 sec)
mysql> create table condmeth (condid int(11), methid int(11), recomm
text);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into cond (cond, sname) values ('Age', 'Age');
Query OK, 1 row affected (0.01 sec)
mysql> insert into meth (meth, sname) values ('Combined OCs', 'COC');
Query OK, 1 row affected (0.00 sec)
mysql> select * from meth;
+--------+--------------+-------+
| methid | meth | sname |
+--------+--------------+-------+
| 1 | Combined OCs | COC |
+--------+--------------+-------+
1 row in set (0.01 sec)
mysql> select * from cond;
+--------+------+-------+
| condid | cond | sname |
+--------+------+-------+
| 1 | Age | Age |
+--------+------+-------+
1 row in set (0.01 sec)
mysql> show fields from cond;
+--------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+----------------+
| condid | int(11) | | PRI | NULL | auto_increment |
| cond | varchar(255) | YES | | NULL | |
| sname | varchar(255) | YES | | NULL | |
+--------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> show fields from meth;
+--------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+----------------+
| methid | int(11) | | PRI | NULL | auto_increment |
| meth | varchar(255) | YES | | NULL | |
| sname | varchar(255) | YES | | NULL | |
+--------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> show fields from condmeth;
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| condid | int(11) | YES | | NULL | |
| methid | int(11) | YES | | NULL | |
| recomm | text | YES | | NULL | |
+--------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into condmeth (methid, condid, recomm) select
meth.methid, cond.condid, 'Recommendation for Age:COC' from meth, cond
where meth.sname='COC' and cond.sname='Age';
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from condmeth;
+--------+--------+----------------------------+
| condid | methid | recomm |
+--------+--------+----------------------------+
| 1 | 1 | Recommendation for Age:COC |
+--------+--------+----------------------------+
1 row in set (0.00 sec)
mysql>
