title: DELETE FROM HAVING COUNT(*) in MySQL
body: <p>Ok so there are couple posts here already on this and fewer still out on the web. I've literally tried every one of them and can not get anything to work. Hopefully someone here can take pity on me :)</p>
  
  <p>Here is the data I'm working with. I want to delete all these records.</p>
  
  <pre><code>SELECT
  part_desc, count(*) as rec_num
  FROM ag_master
  GROUP BY part_desc HAVING COUNT(*) &gt; 1000;
  
  +--------------------------------------+---------+
  | part_desc                            | rec_num |
  +--------------------------------------+---------+
  | SILICON DELAY LINE, TRUE OUTPUT      |    1092 |
  | LOADABLE PLD                         |    1401 |
  | 8-BIT, FLASH, 8 MHz, MICROCONTROLLER |    1411 |
  | FPGA                                 |    1997 |
  | 8-BIT, MROM, 8 MHz, MICROCONTROLLER  |    3425 |
  +--------------------------------------+---------+
  5 rows in set (0.00 sec)
  </code></pre>
  
  <p>The closest I've come to finding code that would do it is shown below. The syntax checks ok and it runs, however it just seems to hang the database up. I've let it run for as long as 10 minutes and nothing ever happens so I abort it.</p>
  
  <pre><code>DELETE
  FROM ag_master
  WHERE part_id IN (
    SELECT part_id
    FROM ag_master
    GROUP BY part_desc
    HAVING COUNT(*) &gt; 1000
  );
  </code></pre>
  
  <p>Here's the explain plan on the tmp table</p>
  
  <pre><code>mysql&gt; EXPLAIN SELECT * FROM ag_master WHERE part_desc IN (SELECT part_desc FROM tmp);
  +----+--------------------+-----------+--------+---------------+------+---------+------+--------+-------------+
  | id | select_type        | table     | type   | possible_keys | key  | key_len | ref  | rows   | Extra       |
  +----+--------------------+-----------+--------+---------------+------+---------+------+--------+-------------+
  |  1 | PRIMARY            | ag_master | ALL    | NULL          | NULL | NULL    | NULL | 177266 | Using where |
  |  2 | DEPENDENT SUBQUERY | tmp       | system | NULL          | NULL | NULL    | NULL |      1 |             |
  +----+--------------------+-----------+--------+---------------+------+---------+------+--------+-------------+
  2 rows in set (0.00 sec)
  </code></pre>