Hi @Martin Kevin
Hi, I have checked relativeid is not unique. Example of Empid-11111. This person has three rows and relativeid show be 1,2 and 3. But it shows 1 (two times) and 3 one time.
Normally, there would not exist duplicate number when using Row_Number
.
How about this:
UPDATE P
SET P.RelativeID = P.New_RelativeID
FROM (
SELECT RelativeID, ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY relativeName) AS New_RelativeID
FROM #Person
) P
Best regards,
Cosmog Hong
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".