Updating Sequence Records for each category using Join

Input:

Id ReqId ProjectId

1 NULL 1

2 NULL 1

3 NULL 2

4 NULL 2

5 NULL 2

6 NULL 3

7 NULL 3

8 NULL 3

9 NULL 3

10 NULL 4

11 NULL 4

Output

Id ReqId ProjectId

1 1 1

2 2 1

3 1 2

4 2 2

5 3 2

6 1 3

7 2 3

8 3 3

9 4 3

10 1 4

11 2 4

;

with cteSequence

as

(

select r.Id,ProjectId,r.DateCreated,row_number() over (partition by ProjectId order by r.DateCreated) as SequenceNo

from Projects p

inner join

Requirements r

ON

p.Id = r.ProjectId

)

UPDATE

Requirements

SET Requirements.RequirementId = s.SequenceNo

FROM

Requirements r

INNER JOIN

cteSequence s

ON r.Id = s.Id

Select ProjectID,ID,RequirementId from Requirements Order By ProjectID,RequirementId,Id