How to update sequence of person if he has relative

Question

Currently RelativeID is blank in database. Ho I can update the RelativeID from sequence. If any Empid has relative then should update the unique RelativeID starting from 1. If someone has two relative then RelativeID will be 1 and 2. If someone has three relative then RelativeID will be 1 , 2 and 3 with the sequence. If someone has only one relative then RelativeID will be 1. I want to use UPDATE command to update the RelativeID column. Please help on this. Thanks

Create table #Person (EmpID char(10), Name char(10), relativeName char(10), relation char(4) RelativeID char(1))

Insert into #person values ('11111','Chris','SS','S','')

Insert into #person values ('11111','Chris','AA,'D','')

Insert into #person values ('11111','Chris','BB','W','')

Insert into #person values ('11122','Sareem','ZS','D','')

Insert into #person values ('11133','Najam','RR','D','')

Insert into #person values ('11133','Najam','RS','W','')

Select * from #Person

--Expected output

Empid Name relativeName relation RelativeID

11111 Chris SS S 2

11111 Chris AA D 3

11111 Chris BB W 1

11122 Sareem ZS D 1

11133 Najam RR D 2

11133 Najam RS W 1

Answer 1

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".

Answer 2

Hi @Martin Kevin

If you want the specified sequence of relative, then try this:

;WITH RelativesCTE AS 
(
 SELECT EmpID,relativeName,
        ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY CHARINDEX(CONVERT(VARCHAR(1),relation),'W,S,D')) AS RelativeID
 FROM #Person
)
UPDATE p
SET p.RelativeID = c.RelativeID
FROM #Person p JOIN RelativesCTE c ON p.EmpID = c.EmpID AND p.relativeName = c.relativeName

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".

Answer 3

Thanks for posting your question in the Microsoft Q&A forum.

You can use a common table expression (CTE) along with the ROW_NUMBER() window function to generate the RelativeID based on the count of relatives for each EmpID. Then you can update the RelativeID column in the #Person table with the generated RelativeID

;WITH RelativesCTE AS (
    SELECT 
        EmpID,
        relativeName,
        ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY (SELECT NULL)) AS RelativeID
    FROM 
        #Person
)
UPDATE p
SET p.RelativeID = c.RelativeID
FROM 
    #Person p
JOIN 
    RelativesCTE c ON p.EmpID = c.EmpID AND p.relativeName = c.relativeName

SELECT * FROM #Person;

• Please remember to drop the temporary table #Person at the end of the process to clean up resources.

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful