title: Oracle merge into for when match update and then delete is removing all row
body: <p>Below is the data i have. I need duplicate row to be removed but parallely update last update user id and last update time stamp.</p>
  
  <p>There are 3 more column below like Primary Key which is unique and Last Update timestamp and last update user Id.</p>
  
  <p><a href="https://i.stack.imgur.com/5haNY.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/5haNY.png" alt="enter image description here"></a></p>
  
  <p>When executing the below query it is deleting all 8 rows instead of 4.
  I am not able to identify what is wrong in the query,</p>
  
  <pre><code>merge into TestTable tgt
  using (select ID,
                Date,AMT,
                Currency,
                Value,
                count(*) over (partition by ID, Date, AMT,Currency,Value ) grp_count,
                row_number() over (partition by ID, Date, AMT,Currency,Value order by ID) rn
       from TestTable 
  )src
  --      where rn &gt; 1) src
  on (tgt.ID = src.ID  and src.rn = 2) 
  when matched then
      update set  tgt.LastUpdtUser= 'testing',tgt.LastUpdateTime = SYSDATE
      where src.rn = 2
      delete where src.rn = 2;    
  </code></pre>
  
  <p>Can anyone please advice. I just need 4 rows to be deleted which has RN = 2 and the row with RN = 1 should have updated timestamp.</p>
  
  <p>Also in real database there are many non duplicate records also. We dont want anything to do in those.</p>