From: Justin (mysqlb0rker.com)
Date: Wed Sep 05 2007 - 08:41:58 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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" <steinoccri.net>
To: "MySql" <mysqllists.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/mysql?unsub=mysqlb0rker.com
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]