How to update sequence of person if he has relative

Martin Kevin 241 Reputation points
2024-04-17T22:26:20.6+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,948 questions
0 comments No comments
{count} votes

Accepted answer
  1. LiHongMSFT-4306 27,881 Reputation points
    2024-04-19T02:00:07.27+00:00

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


2 additional answers

Sort by: Most helpful
  1. LiHongMSFT-4306 27,881 Reputation points
    2024-04-18T02:12:05.5066667+00:00

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

    1 person found this answer helpful.

  2. hossein jalilian 8,080 Reputation points
    2024-04-17T22:38:52.95+00:00

    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


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.