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