Re: delete query question
If the tables are InnoDB, you could temporarily set up a foreign key relationship between the two, with the 'ON DELETE CASCADE' option. On Tue, 2008-07-08 at 11:14 -0400, Jeff Mckeon wrote: I think this is possible but I'm having a total brain fart as to how to construct the query.. Table2.ticket = table1.ID Table2 is a many to 1 relationship to table1 I need to delete all records from table1 where created unix_timestamp(date_sub(now(), interval 3 month)) And all rows from table2 where Table2.ticket = Table1.ID (of the deleted rows..) Can't this be done in one query? Or two? Thanks, Jeff -- Ian Simpson System Administrator MyJobGroup This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements and it is your responsibility to scan any attachments.
RE: delete query question
-Original Message- From: Ian Simpson [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 08, 2008 11:27 AM To: Jeff Mckeon Cc: mysql@lists.mysql.com Subject: Re: delete query question If the tables are InnoDB, you could temporarily set up a foreign key relationship between the two, with the 'ON DELETE CASCADE' option. Nope, MyISAM... On Tue, 2008-07-08 at 11:14 -0400, Jeff Mckeon wrote: I think this is possible but I'm having a total brain fart as to how to construct the query.. Table2.ticket = table1.ID Table2 is a many to 1 relationship to table1 I need to delete all records from table1 where created unix_timestamp(date_sub(now(), interval 3 month)) And all rows from table2 where Table2.ticket = Table1.ID (of the deleted rows..) Can't this be done in one query? Or two? Thanks, Jeff -- Ian Simpson System Administrator MyJobGroup This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements and it is your responsibility to scan any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: delete query question
Oh well ;) It looks like you can use joins in a delete statement, and delete the joined rows, which will delete from the individual tables. So something like: delete table1, table2 from table1 inner join table2 on table1.ID = table2.ticket where... should do it I modified the above code from http://dev.mysql.com/doc/refman/5.0/en/delete.html just search in the page for 'join' and you'll find the relevant section On Tue, 2008-07-08 at 11:35 -0400, Jeff Mckeon wrote: -Original Message- From: Ian Simpson [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 08, 2008 11:27 AM To: Jeff Mckeon Cc: mysql@lists.mysql.com Subject: Re: delete query question If the tables are InnoDB, you could temporarily set up a foreign key relationship between the two, with the 'ON DELETE CASCADE' option. Nope, MyISAM... On Tue, 2008-07-08 at 11:14 -0400, Jeff Mckeon wrote: I think this is possible but I'm having a total brain fart as to how to construct the query.. Table2.ticket = table1.ID Table2 is a many to 1 relationship to table1 I need to delete all records from table1 where created unix_timestamp(date_sub(now(), interval 3 month)) And all rows from table2 where Table2.ticket = Table1.ID (of the deleted rows..) Can't this be done in one query? Or two? Thanks, Jeff -- Ian Simpson System Administrator MyJobGroup This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements and it is your responsibility to scan any attachments. -- Ian Simpson System Administrator MyJobGroup This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements and it is your responsibility to scan any attachments.
Re: delete query question
Jeff, Table2.ticket = table1.ID Table2 is a many to 1 relationship to table1 I need to delete all records from table1 where created unix_timestamp(date_sub(now(), interval 3 month)) And all rows from table2 where Table2.ticket = Table1.ID (of the deleted rows..) Like this (untested)? DELETE table1,table2 FROM table1 t1 JOIN table2 t2 ON t1.id=t2.ticket WHERE t2.created UNIX_TIMESTAMP( DATE_SUB( NOW(), INTERVAL 3 MONTH )) ; PB - Jeff Mckeon wrote: I think this is possible but I'm having a total brain fart as to how to construct the query.. Table2.ticket = table1.ID Table2 is a many to 1 relationship to table1 I need to delete all records from table1 where created unix_timestamp(date_sub(now(), interval 3 month)) And all rows from table2 where Table2.ticket = Table1.ID (of the deleted rows..) Can't this be done in one query? Or two? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: delete query question
Thanks, that did it! -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 08, 2008 11:57 AM To: Jeff Mckeon Cc: mysql@lists.mysql.com Subject: Re: delete query question Jeff, Table2.ticket = table1.ID Table2 is a many to 1 relationship to table1 I need to delete all records from table1 where created unix_timestamp(date_sub(now(), interval 3 month)) And all rows from table2 where Table2.ticket = Table1.ID (of the deleted rows..) Like this (untested)? DELETE table1,table2 FROM table1 t1 JOIN table2 t2 ON t1.id=t2.ticket WHERE t2.created UNIX_TIMESTAMP( DATE_SUB( NOW(), INTERVAL 3 MONTH )) ; PB - Jeff Mckeon wrote: I think this is possible but I'm having a total brain fart as to how to construct the query.. Table2.ticket = table1.ID Table2 is a many to 1 relationship to table1 I need to delete all records from table1 where created unix_timestamp(date_sub(now(), interval 3 month)) And all rows from table2 where Table2.ticket = Table1.ID (of the deleted rows..) Can't this be done in one query? Or two? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delete query question
try SELECT * from geno_260k WHERE ident IN (SELECT ident FROM geno_260k WHERE a1=0 GROUP BY ident HAVING count(a1)25); This will give you what you're deleting first.. then if that is good. do DELETE FROM geno_260k WHERE ident IN (SELECT ident FROM geno_260k WHERE a1=0 GROUP BY ident HAVING count(a1)25); (note the change in case is just my way of seeing things.. it's not necessary that I know of) - Original Message - From: Olaf Stein [EMAIL PROTECTED] To: MySql mysql@lists.mysql.com Sent: Wednesday, September 05, 2007 9:35 AM Subject: Delete query question Hey all I am stuck here (thinking wise) and need some ideas: I have this table: CREATE TABLE `geno_260k` ( `genotype_id` int(10) unsigned NOT NULL auto_increment, `ident` int(10) unsigned NOT NULL, `marker_id` int(10) unsigned NOT NULL, `a1` tinyint(3) unsigned NOT NULL, `a2` tinyint(3) unsigned NOT NULL default '0', PRIMARY KEY (`genotype_id`), KEY `ident` (`ident`), KEY `marker_id` (`marker_id`), CONSTRAINT `geno_260k_ibfk_2` FOREIGN KEY (`marker_id`) REFERENCES `markers` (`marker_id`), CONSTRAINT `geno_260k_ibfk_1` FOREIGN KEY (`ident`) REFERENCES `individual` (`ident`) ) ENGINE=InnoDB AUTO_INCREMENT=91318273 DEFAULT CHARSET=utf8 And with the following query I get 159 ident's back: select ident from geno_260k where a1=0 group by ident having count(a1)25; I want to delete all records containing those idents (about 26 per ident so 159*26). So I thought delete from geno_260k where ident=(select ident from geno_260k where a1=0 group by ident having count(a1)25); But mysql can not select and delete from the same table. Any ideas? Thanks Olaf -- 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]
Re: Delete query question
reply inline On 9/5/07, Olaf Stein [EMAIL PROTECTED] wrote: delete from geno_260k where ident=(select ident from geno_260k where a1=0 group by ident having count(a1)25); When a sub query returns more than one row in a where clause, then = should be replaced by the in . -- Thanks Alex http://alexlurthu.wordpress.com
Re: Delete query question
Thanks, This seems to work but that IN seems to be really slow... On 9/5/07 9:41 AM, Justin [EMAIL PROTECTED] wrote: try SELECT * from geno_260k WHERE ident IN (SELECT ident FROM geno_260k WHERE a1=0 GROUP BY ident HAVING count(a1)25); This will give you what you're deleting first.. then if that is good. do DELETE FROM geno_260k WHERE ident IN (SELECT ident FROM geno_260k WHERE a1=0 GROUP BY ident HAVING count(a1)25); (note the change in case is just my way of seeing things.. it's not necessary that I know of) - Original Message - From: Olaf Stein [EMAIL PROTECTED] To: MySql mysql@lists.mysql.com Sent: Wednesday, September 05, 2007 9:35 AM Subject: Delete query question Hey all I am stuck here (thinking wise) and need some ideas: I have this table: CREATE TABLE `geno_260k` ( `genotype_id` int(10) unsigned NOT NULL auto_increment, `ident` int(10) unsigned NOT NULL, `marker_id` int(10) unsigned NOT NULL, `a1` tinyint(3) unsigned NOT NULL, `a2` tinyint(3) unsigned NOT NULL default '0', PRIMARY KEY (`genotype_id`), KEY `ident` (`ident`), KEY `marker_id` (`marker_id`), CONSTRAINT `geno_260k_ibfk_2` FOREIGN KEY (`marker_id`) REFERENCES `markers` (`marker_id`), CONSTRAINT `geno_260k_ibfk_1` FOREIGN KEY (`ident`) REFERENCES `individual` (`ident`) ) ENGINE=InnoDB AUTO_INCREMENT=91318273 DEFAULT CHARSET=utf8 And with the following query I get 159 ident's back: select ident from geno_260k where a1=0 group by ident having count(a1)25; I want to delete all records containing those idents (about 26 per ident so 159*26). So I thought delete from geno_260k where ident=(select ident from geno_260k where a1=0 group by ident having count(a1)25); But mysql can not select and delete from the same table. Any ideas? Thanks Olaf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] - Olaf Stein DBA Center for Quantitative and Computational Biology Columbus Children's Research Institute 700 Children's Drive phone: 1-614-355-5685 cell: 1-614-843-0432 email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delete query question
Thanks baron, I will try this just for test purposes as I already wrote a script, which is slow but not as bad as using IN() Olaf On 9/5/07 3:29 PM, Baron Schwartz [EMAIL PROTECTED] wrote: IN() subqueries in MySQL are badly optimized. It's usually better to use a JOIN, even though it's non-standard: DELETE geno_260k.* FROM geno_260k INNER JOIN ( SELECT ident FROM geno_260k WHERE a1=0 GROUP BY ident HAVING count(*)25 ) AS der USING(ident); Try profiling this and see if it's faster. It probably will be on any reasonably large data set, as long as the table has an index on ident. Note I changed the COUNT(a1) to COUNT(*) for efficiency. Counting a column counts the number of values (e.g. non-null). Counting * just counts the number of rows and can be faster. COUNT(*) is what you want to use 99% of the time. Regards Baron Olaf Stein wrote: Thanks, This seems to work but that IN seems to be really slow... On 9/5/07 9:41 AM, Justin [EMAIL PROTECTED] wrote: try SELECT * from geno_260k WHERE ident IN (SELECT ident FROM geno_260k WHERE a1=0 GROUP BY ident HAVING count(a1)25); This will give you what you're deleting first.. then if that is good. do DELETE FROM geno_260k WHERE ident IN (SELECT ident FROM geno_260k WHERE a1=0 GROUP BY ident HAVING count(a1)25); (note the change in case is just my way of seeing things.. it's not necessary that I know of) - Original Message - From: Olaf Stein [EMAIL PROTECTED] To: MySql mysql@lists.mysql.com Sent: Wednesday, September 05, 2007 9:35 AM Subject: Delete query question Hey all I am stuck here (thinking wise) and need some ideas: I have this table: CREATE TABLE `geno_260k` ( `genotype_id` int(10) unsigned NOT NULL auto_increment, `ident` int(10) unsigned NOT NULL, `marker_id` int(10) unsigned NOT NULL, `a1` tinyint(3) unsigned NOT NULL, `a2` tinyint(3) unsigned NOT NULL default '0', PRIMARY KEY (`genotype_id`), KEY `ident` (`ident`), KEY `marker_id` (`marker_id`), CONSTRAINT `geno_260k_ibfk_2` FOREIGN KEY (`marker_id`) REFERENCES `markers` (`marker_id`), CONSTRAINT `geno_260k_ibfk_1` FOREIGN KEY (`ident`) REFERENCES `individual` (`ident`) ) ENGINE=InnoDB AUTO_INCREMENT=91318273 DEFAULT CHARSET=utf8 And with the following query I get 159 ident's back: select ident from geno_260k where a1=0 group by ident having count(a1)25; I want to delete all records containing those idents (about 26 per ident so 159*26). So I thought delete from geno_260k where ident=(select ident from geno_260k where a1=0 group by ident having count(a1)25); But mysql can not select and delete from the same table. Any ideas? Thanks Olaf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] - Olaf Stein DBA Center for Quantitative and Computational Biology Columbus Children's Research Institute 700 Children's Drive phone: 1-614-355-5685 cell: 1-614-843-0432 email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delete query question
IN() subqueries in MySQL are badly optimized. It's usually better to use a JOIN, even though it's non-standard: DELETE geno_260k.* FROM geno_260k INNER JOIN ( SELECT ident FROM geno_260k WHERE a1=0 GROUP BY ident HAVING count(*)25 ) AS der USING(ident); Try profiling this and see if it's faster. It probably will be on any reasonably large data set, as long as the table has an index on ident. Note I changed the COUNT(a1) to COUNT(*) for efficiency. Counting a column counts the number of values (e.g. non-null). Counting * just counts the number of rows and can be faster. COUNT(*) is what you want to use 99% of the time. Regards Baron Olaf Stein wrote: Thanks, This seems to work but that IN seems to be really slow... On 9/5/07 9:41 AM, Justin [EMAIL PROTECTED] wrote: try SELECT * from geno_260k WHERE ident IN (SELECT ident FROM geno_260k WHERE a1=0 GROUP BY ident HAVING count(a1)25); This will give you what you're deleting first.. then if that is good. do DELETE FROM geno_260k WHERE ident IN (SELECT ident FROM geno_260k WHERE a1=0 GROUP BY ident HAVING count(a1)25); (note the change in case is just my way of seeing things.. it's not necessary that I know of) - Original Message - From: Olaf Stein [EMAIL PROTECTED] To: MySql mysql@lists.mysql.com Sent: Wednesday, September 05, 2007 9:35 AM Subject: Delete query question Hey all I am stuck here (thinking wise) and need some ideas: I have this table: CREATE TABLE `geno_260k` ( `genotype_id` int(10) unsigned NOT NULL auto_increment, `ident` int(10) unsigned NOT NULL, `marker_id` int(10) unsigned NOT NULL, `a1` tinyint(3) unsigned NOT NULL, `a2` tinyint(3) unsigned NOT NULL default '0', PRIMARY KEY (`genotype_id`), KEY `ident` (`ident`), KEY `marker_id` (`marker_id`), CONSTRAINT `geno_260k_ibfk_2` FOREIGN KEY (`marker_id`) REFERENCES `markers` (`marker_id`), CONSTRAINT `geno_260k_ibfk_1` FOREIGN KEY (`ident`) REFERENCES `individual` (`ident`) ) ENGINE=InnoDB AUTO_INCREMENT=91318273 DEFAULT CHARSET=utf8 And with the following query I get 159 ident's back: select ident from geno_260k where a1=0 group by ident having count(a1)25; I want to delete all records containing those idents (about 26 per ident so 159*26). So I thought delete from geno_260k where ident=(select ident from geno_260k where a1=0 group by ident having count(a1)25); But mysql can not select and delete from the same table. Any ideas? Thanks Olaf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] - Olaf Stein DBA Center for Quantitative and Computational Biology Columbus Children's Research Institute 700 Children's Drive phone: 1-614-355-5685 cell: 1-614-843-0432 email: [EMAIL PROTECTED] -- Baron Schwartz Xaprb LLC http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
