Perhaps not the most elegant way: - Create a temporary table - Select-insert into the temp-table - Use the temp-table for a delete-join or a 'NOT IN'-statement or something like that
------------------------------------------------ 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)>250000; I want to delete all records containing those idents (about 260000 per ident so 159*260000). So I thought delete from geno_260k where ident=(select ident from geno_260k where a1=0 group by ident having count(a1)>250000); 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]
