Re: insert subquery
2005/9/23, Gordon Bruce [EMAIL PROTECTED]: What am I missing INSERT INTO table1 (column names.) SELECT VALUES.. FROM table2 WHERE primary id = insert value You will have to put in your real table name and column names. nice and easy :-) -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert subquery
DJ [EMAIL PROTECTED] wrote on 09/23/2005 12:22:58 PM: i want to insert a row into table1 only if the value being inserted on table1 exists on table2 primary id. can i do this with subquery? thanx. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.5/110 - Release Date: 9/22/2005 Depending on what version MySQL you are using, probably not. A very robust method of doing what you propose is to allow MySQL to do it for you by establishing a Foreign Key from table1 to table2. One drawback is that both tables need to be InnoDB (which you may not want to support). What version are you using and what is the possibility of using InnoDB with your appliation? Shawn Green Database Administrator Unimin Corporation - Spruce Pine PS: always CC the list on all responses (unless you intentionally mean to take the conversation off-list)
Re: insert subquery
[EMAIL PROTECTED] wrote: DJ wrote on 09/23/2005 12:22:58 PM: i want to insert a row into table1 only if the value being inserted on table1 exists on table2 primary id. can i do this with subquery? thanx. Depending on what version MySQL you are using, probably not. A very robust method of doing what you propose is to allow MySQL to do it for you by establishing a Foreign Key from table1 to table2. One drawback is that both tables need to be InnoDB (which you may not want to support). What version are you using and what is the possibility of using InnoDB with your appliation? Shawn Green Database Administrator Unimin Corporation - Spruce Pine PS: always CC the list on all responses (unless you intentionally mean to take the conversation off-list) i am using 4.1.x hmm.. maybe it's easier if i just check the id is in table2 before inserting into table1. not really a big deal just looking to create shortcuts without running multiple queries.. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.5/110 - Release Date: 9/22/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert subquery
DJ [EMAIL PROTECTED] wrote on 09/23/2005 12:49:35 PM: [EMAIL PROTECTED] wrote: DJ wrote on 09/23/2005 12:22:58 PM: i want to insert a row into table1 only if the value being inserted on table1 exists on table2 primary id. can i do this with subquery? thanx. Depending on what version MySQL you are using, probably not. A very robust method of doing what you propose is to allow MySQL to do it for you by establishing a Foreign Key from table1 to table2. One drawback is that both tables need to be InnoDB (which you may not want to support). What version are you using and what is the possibility of using InnoDB with your appliation? Shawn Green Database Administrator Unimin Corporation - Spruce Pine PS: always CC the list on all responses (unless you intentionally mean to take the conversation off-list) i am using 4.1.x hmm.. maybe it's easier if i just check the id is in table2 before inserting into table1. not really a big deal just looking to create shortcuts without running multiple queries.. With a foreign key defined, you only need to run one query. That's why I mentioned it. ;-) Your way works, too. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: insert subquery
What am I missing INSERT INTO table1 (column names.) SELECT VALUES.. FROM table2 WHERE primary id = insert value You will have to put in your real table name and column names. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, September 23, 2005 11:59 AM To: DJ Cc: mysql@lists.mysql.com Subject: Re: insert subquery DJ [EMAIL PROTECTED] wrote on 09/23/2005 12:49:35 PM: [EMAIL PROTECTED] wrote: DJ wrote on 09/23/2005 12:22:58 PM: i want to insert a row into table1 only if the value being inserted on table1 exists on table2 primary id. can i do this with subquery? thanx. Depending on what version MySQL you are using, probably not. A very robust method of doing what you propose is to allow MySQL to do it for you by establishing a Foreign Key from table1 to table2. One drawback is that both tables need to be InnoDB (which you may not want to support). What version are you using and what is the possibility of using InnoDB with your appliation? Shawn Green Database Administrator Unimin Corporation - Spruce Pine PS: always CC the list on all responses (unless you intentionally mean to take the conversation off-list) i am using 4.1.x hmm.. maybe it's easier if i just check the id is in table2 before inserting into table1. not really a big deal just looking to create shortcuts without running multiple queries.. With a foreign key defined, you only need to run one query. That's why I mentioned it. ;-) Your way works, too. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: insert subquery
Well, Gordon, looks like you missed the thread. ;-) What's happening is that DJ wants to add records to a table only if another table has a certain record in it. I have been trying to convince him to set up a FOREIGN KEY between the two. Since he didn't give any real table name I will have to make up a situation to illustrate his problem: Your boss sees productivity numbers going into the tank and asks you to produce a report of how much time certain employees spend on the phone. You can get from the phone system a log of every call made into or out of your company that shows you what extension was active, when it went hot, when it hung up, the outside number or extension of the other end of the call, and which end initiated the call. Being a good DBA, you break out your database skills and start coding. For this example, lets create two tables, WatchList and PhoneLog. The WatchList table provides an id value for everyone you are interested in tracking calls for. You get call records for everyone in your company but you only want to write into PhoneLog those phone calls either to or from those specific extensions on the WatchList. You want to make sure that if you add a record to PhoneLog, you have a WatchList record already defined otherwise the watchlist_id field would have to be NULL doing you absolutely no good to getting to the bottom of the issue. CREATE TABLE WatchList ( id int auto_increment , name varchar(20) , ext int ,Primary Key(id) ); CREATE TABLE PhoneLog ( id int auto_increment , watchlist_id int , direction varchar(4) , number varchar(25) , startdatetime datetime , enddatetime datetime , PRIMARY KEY(id) ); Some partial data could look like: WatchList (id, name, ext) - 1, Shawn, 101 2, Gordon , 102 3, DJ, 103 PhoneLog (id, watchlist_id, direction, number, startdatetime, stopdatetime) -- 1, 2, from, 555-0404, ... 1,2, to, 555-9897, ... What DJ would like to do is to detect if extension 101 (Shawn) has a record in WatchList so that he could know whether or not to add the phone call event to the PhoneLog table. Preferably he wants to do this in a single query but unless he sets up a Foreign Key from PhoneLog to WatchList he will have to make that determination by using a second query. His original question was asking if he could use a subquery to avoid the second check. I said probably not. Does that help you understand what you missed? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Gordon Bruce [EMAIL PROTECTED] wrote on 09/23/2005 03:26:05 PM: What am I missing INSERT INTO table1 (column names.) SELECT VALUES.. FROM table2 WHERE primary id = insert value You will have to put in your real table name and column names. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, September 23, 2005 11:59 AM To: DJ Cc: mysql@lists.mysql.com Subject: Re: insert subquery DJ [EMAIL PROTECTED] wrote on 09/23/2005 12:49:35 PM: [EMAIL PROTECTED] wrote: DJ wrote on 09/23/2005 12:22:58 PM: i want to insert a row into table1 only if the value being inserted on table1 exists on table2 primary id. can i do this with subquery? thanx. Depending on what version MySQL you are using, probably not. A very robust method of doing what you propose is to allow MySQL to do it for you by establishing a Foreign Key from table1 to table2. One drawback is that both tables need to be InnoDB (which you may not want to support). What version are you using and what is the possibility of using InnoDB with your appliation? Shawn Green Database Administrator Unimin Corporation - Spruce Pine PS: always CC the list on all responses (unless you intentionally mean to take the conversation off-list) i am using 4.1.x hmm.. maybe it's easier if i just check the id is in table2 before inserting into table1. not really a big deal just looking to create shortcuts without running multiple queries.. With a foreign key defined, you only need to run one query. That's why I mentioned it. ;-) Your way works, too. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: insert subquery
[EMAIL PROTECTED] wrote:
Well, Gordon, looks like you missed the thread. ;-)
Maybe, but his solution is actually pretty close to what the OP wanted
Instead of
INSERT INTO Table1 (...) VALUES ('val1', 'val2', 'val3', ...) something
you do
INSERT INTO Table1 (...)
SELECT 'val1', 'val2', 'val3', ...
FROM Table2 WHERE Table2.keycol = 'valtocheck'
Example:
[...CSIMain/csi] 27. mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.14-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql use test
Database changed
mysql create table table1(v1 varchar(20), v2 varchar(20));
Query OK, 0 rows affected (0.18 sec)
mysql create table table2(k1 varchar(20) primary key, v1 varchar(20));
Query OK, 0 rows affected (0.08 sec)
mysql insert into table2 values('1', 'v1');
Query OK, 1 row affected (0.04 sec)
mysql insert into table2 values('2', 'v2');
Query OK, 1 row affected (0.04 sec)
NOW, you want to insert some set of values only if some value (one of
the ones being inserted, or something else in this example) is in table2:
mysql insert into table1 select 's1', 's2' from table2 where k1 = '0';
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql insert into table1 select 's1', 's2' from table2 where k1 = '1';
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0
Voila!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
