title: How to delete duplicate rows from an Oracle Database?
body: <p>We have a table that has had the same data inserted into it twice by accident meaning most (but not all) rows appears twice in the table. Simply put, I'd like an SQL statement to delete one version of a row while keeping the other; I don't mind which version is deleted as they're identical.</p>
  
  <p>Table structure is something like:</p>
  
  <blockquote>
    <p>FID, unique_ID, COL3, COL4....</p>
  </blockquote>
  
  <p><code>Unique_ID</code> is the primary key, meaning each one appears only once.
  <code>FID</code> is a key that is unique to each feature, so if it appears more than once then the duplicates should be deleted.</p>
  
  <p>To select features that have duplicates would be:</p>
  
  <p><code>select count(*) from TABLE GROUP by FID</code></p>
  
  <p>Unfortunately I can't figure out how to go from that to a SQL delete statement that will delete extraneous rows leaving only one of each.</p>
  
  <p>This sort of question has been asked before, and I've tried the create table with distinct, but how do I get all columns without naming them? This only gets the single column FID and itemising all the columns to keep gives an: <code>ORA-00936: missing expression</code></p>
  
  <p><code>CREATE TABLE secondtable NOLOGGING as select distinct FID from TABLE</code></p>