yope yope - 1 month ago 8
SQL Question

Setting occurrence number in Sqlserver

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
working screenshot

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
not working

Answer Source

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 |
+-----------------+-----------+---------------+---------------+------------+