AW: Delete query question

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

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

Do you need help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related lists.mysql.com Links announce benchmarks bugs certification cluster commits dotnet gui-tools internals java maxdb myodbc mysql mysqldoc packagers perl plusplus replication win32 Request more information about Pantek

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/mysql?unsub=lists@pantek.com