title: Oracle 11g insert into select from a table with duplicate rows
body: <p>I have one table that need to split into several other tables.
  But the main table is just like a transitive table.
  I dump data from a excel into it (from 5k to 200k rows) , and using insert into select, split into the correct tables (Five different tables).
  However, the latest dataset that my client sent has records with duplicates values.</p>
  <p>The primary key usually is ENI for my table. But even this record is duplicated because the same company can be a customer and a service provider, so they have two different registers but use the same ENI.</p>
  <p>What i have so far.
  I found a script that uses merge and modified it to find same eni and update the same main_id to all</p>
  <pre><code>|Main_id|   ENI     |   company_name|   Type
  |   1   |   1864    |   JOHN        |   C
  |   2   |   351485  |   JOEL        |   C
  |   3   |   16546   |   MICHEL      |   C
  |   2   |   351485  |   JOEL J.     |   S
  |   1   |   1864    |   JOHN E. E.  |   C
  
  Main_id: Primarykey that the main BD uses
  ENI: Unique company number
  Type: 'C' - COSTUMER 'S' - SERVICE PROVIDERR
  </code></pre>
  <p>Some Cases it can have the same type. just like id 1
  there are several other Columns...</p>
  <p>What i need:
  insert any of the main_id my other script already sorted, and set a flag on the others that they were not inserted. i cant delete any data i'll need to send these info to the costumer validate.</p>
  <p>or i just simply cant make this way and go back to the good old excel</p>
  <p>Edit: as a question below this is a example</p>
  <pre><code>|Main_id|   ENI     |   company_name|   Type|   RANK|
  |   1   |   1864    |   JOHN        |   C   |   1   |
  |   2   |   351485  |   JOEL        |   C   |   1   |
  |   3   |   16546   |   MICHEL      |   C   |   1   |
  |   2   |   351485  |   JOEL J.     |   S   |   2   |
  |   1   |   1864    |   JOHN E. E.  |   C   |   2   |
  
      RANK -  would be like the 1864 appears 2 times,
  1st one found gets 1 second 2 and so on. i tryed using
      RANK() OVER (PARTITION BY MAIN_ID ORDER BY ENI)
      RANK() OVER (PARTITION BY company_name ORDER BY ENI)
  </code></pre>
  <p><strong>Thanks to TEJASH i was able to come up with this solution</strong></p>
  <pre><code>MERGE INTO TABLEA S
      USING (Select ROWID AS ID,
          row_number() Over(partition by eniorder by eni, type) as RANK_DUPLICATED
          From TABLEA
      ) T
      ON (S.ROWID = T.ID)
      WHEN MATCHED THEN UPDATE SET S.RANK_DUPLICATED= T.RANK_DUPLICATED;
  </code></pre>