title: Delete duplicate rows having count greater than 1 in mysql
body: <p>Need some help on a MySQL query to delete the rows where the userID count is greater than one and has an acl value=4. </p>
  
  <p>What I've tried as the select query:</p>
  
  <pre><code>DELETE FROM `users_acl` 
  WHERE userID IN(
      SELECT * FROM `users_acl` 
      WHERE `acl` BETWEEN 2 AND 4 
      GROUP BY userID 
      HAVING COUNT(userID) &gt; 1
  );
  </code></pre>
  
  <p>But can't find a proper way to delete the rows in the same query.</p>
  
  <p>Table structure</p>
  
  <pre><code>+--------+-----+---------+
  | userID | acl | deleted |
  +--------+-----+---------+
  |     27 |   2 |       0 |
  |     28 |   2 |       0 |
  |     31 |   2 |       0 |
  |     42 |   2 |       0 |
  |     42 |   4 |       0 |
  |     45 |   1 |       0 |
  |     51 |   1 |       0 |
  |     54 |   1 |       0 |
  |     63 |   2 |       0 |
  |     63 |   4 |       0 |
  |     64 |   1 |       0 |
  |     69 |   2 |       0 |
  |     73 |   2 |       0 |
  |     73 |   4 |       0 |
  |     76 |   1 |       0 |
  |     77 |   2 |       0 |
  |     77 |   4 |       0 |
  +--------+-----+---------+
  </code></pre>