try
SELECT * from geno_260k WHERE ident IN (SELECT ident FROM geno_260k WHERE
a1=0
GROUP BY ident HAVING count(a1)>250000);
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)>250000);
(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)>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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
