title: Need to delete duplicate records from the table using row_number()
body: <p>I am having a table test having data as follows and I want to delete the trsid 124 and I have millions entry in my DB it is just a scenarion. Concept is to delete the duplicate entry from the table</p>
  
  <pre><code>--------------------------------------------
  TrsId   |   ID  |   Name    |
  --------------------------------------------    
  123     |   1   |   ABC     |   
  124     |   1   |   ABC     |
  </code></pre>
  
  <p>I am trying something like </p>
  
  <pre><code>delete from test
  select T.* from
  (
      select ROW_NUMBER() over (partition by ID order by name) as r,
             Trsid,
             ID,
             name
      from test
  ) t
  where r = 2
  </code></pre>
  
  <p>Even if I update the query which is Ok for me</p>
  
  <pre><code>update test set id=NULL
  select T.* from
  (
      select ROW_NUMBER() over (partition by ID order by name) as r,
             Trsid,
             ID,
             name
      from test
  ) t
  where r = 2
  </code></pre>
  
  <p>But if i run both this query it deletes all the records from table test. And if i update it update both the records.
  I dont know what I am doing wrong here</p>