title: Removing duplicate SQL records to permit a unique key
body: <p>I have a table ('sales') in a MYSQL DB which should rightfully have had a unique constraint enforced to prevent duplicates. To first remove the dupes and set the constraint is proving a bit tricky. </p>
  
  <p>Table structure (simplified):</p>
  
  <ul>
  <li>'id (unique, autoinc)'</li>
  <li>product_id</li>
  </ul>
  
  <p>The goal is to enforce uniqueness for product_id. The de-duping policy I want to apply is to remove all duplicate records except the most recently created, eg: the highest id. </p>
  
  <p>Or to put another way, I would like to delete only duplicate records, excluding the ids matched by the following query whilst also preserving the existing non-duped records:</p>
  
  <pre><code>select id 
    from sales s  
  inner join (select product_id, 
                     max(id) as maxId 
                from sales 
            group by product_id 
              having count(product_id)  &gt; 1) groupedByProdId on s.product_id 
                                                            and s.id = groupedByProdId.maxId
  </code></pre>
  
  <p>I've struggled with this on two fronts - writing the query to select the correct records to delete and then also the constraint in MYSQL where a subselect FROM clause of a DELETE cannot reference the same table from which data is being removed.</p>
  
  <p>I checked out <a href="https://stackoverflow.com/questions/2759672/how-to-remove-duplicate-records-in-a-table">this</a> answer and it seemed to deal with the subject, but seem specific to sql-server, though I wouldn't rule this question out from duplicating another.</p>