title: Setting occurrence number in Sqlserver
body: <p>Can anyone check my sql query, when using a select statement, the occurrence_number column shows correct values.</p>
  
  <pre><code>select t.[Employee Number], t.Document_Type, t.Document_Name,
   row_number() over (partition by document_type
                            order by right(document_name, 6)
                           ) as occurrence_number
  from BluePrismProcesses.dbo.PID0147_DM t
  </code></pre>
  
  <p><strong>Working</strong>
  <img src="https://i.stack.imgur.com/oPy9z.jpg" alt="working screenshot"></p>
  
  <p>when I using update statement, the occurrence number messed up.</p>
  
  <pre><code>update BluePrismProcesses.dbo.PID0147_DM
  set Occurrence = R.occurrence_number
  from (select t.[Employee Number], t.Document_Type, t.Document_Name,
   row_number() over (partition by document_type
                            order by right(document_name, 6)
                           ) as occurrence_number
  from BluePrismProcesses.dbo.PID0147_DM t) R
  where BluePrismProcesses.dbo.PID0147_DM.Document_Name = r.Document_Name 
  </code></pre>
  
  <p><strong>Not working</strong>
   <img src="https://i.stack.imgur.com/yrmoj.jpg" alt="not working"></p>