title: Mysql - deleting second entry
body: <p>We have a mysql database table that has some erroneous entries that need to be removed. The account table has the following fields: id, code, user_id</p>
  
  <p>Basically, each user should only have one record in the account table for each account. However, at the moment, each user has two records in the account table where the code is equal to 1000.</p>
  
  <p>For every record in the account table, I need to remove the second row where the code = 1000 for each user.</p>
  
  <p>The following query give me all records where the code = 1000. It also contains a count on the account_detail table to confirm that the second erroneous entry does not contain any child records in the account_detail table, and this has been confirmed as correct.</p>
  
  <pre><code>SELECT a.*, COUNT(ad.id)
  FROM account a
  LEFT JOIN account_detail ad on a.id = ad.account_id
  WHERE a.code = 1000
  GROUP by a.id
  ORDER BY a.user_id, a.id
  </code></pre>
  
  <p>So, now I basically need a query that will delete the second erroneous entry for each user_id in the account table where code = 1000</p>