Setting occurrence number in Sqlserver

Question

Can anyone check my sql query, when using a select statement, the occurrence_number column shows correct values.

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

Working

when I using update statement, the occurrence number messed up.

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 

Not working

Answer 1

The problem is you have a duplicate row with the same document_name so that where clause is not working how you might expect.

You can get around this using a common table expression and updating that cte directly like so:

;with cte as (
  select 
      t.[Employee Number]
    , t.Document_Type
    , t.Document_Name
    , t.occurrence
    , occurrence_number = row_number() over (
        partition by document_type 
        order by right(document_name, 6)
      )
   from BluePrismProcesses.dbo.PID0147_DM t
)

update cte
  set occurrence =  occurrence_number
where (occurrence <> occurrence_number or occurrence is null);

---

rextester demo: http://rextester.com/ZVO11388

returns:

+-----------------+-----------+---------------+---------------+------------+
| Employee Number | Doc_Count | Document_Type | Document_Name | Occurrence |
+-----------------+-----------+---------------+---------------+------------+
|          406453 |         2 | offer         |        092735 |          1 |
|          406453 |         2 | offer         |        092735 |          2 |
|          406453 |         2 | offer         |        092848 |          3 |
|          406453 |         2 | offer         |        092848 |          4 |
|          406453 |         2 | offer         |        092848 |          5 |
+-----------------+-----------+---------------+---------------+------------+