How to update group no incremental by 1 based on every group from 1 to 3? I work on SQL server 2012 I face issue i can't update group no to every steps from 1 to 3
meaning i need every group from 1 to 3 take identity number incremental by 1 as 1,2,3 etc ..
create table #Replacement
       (
       PartIDC  INT,
       PartIDX  INT,
       FlagStatus nvarchar(50),
       HasReplacement nvarchar(50),
       groupId int,
       step tinyint
       )
    insert into #Replacement (PartIDC,PartIDX,FlagStatus,HasReplacement,groupId,step)
    values 
    
 (1222, 3421, 'Invalid', 'Critical', NULL, 1),
 (3421, 6421, 'Valid' ,    'Active' ,   NULL, 2),
 (1222, 6421, 'Valid' ,    'Chain',     NULL, 3),
 (5643, 2243, 'Invalid', 'Critical', NULL, 1),
 (2243, 3491, 'Valid' ,    'Active',   NULL, 2),
 (5643, 3491, 'Valid' ,    'Chain',     NULL, 3)

select * from #Replacement
Expected result
   PartIDC PartIDX FlagStatus HasReplacement groupId step
    1222 3421 Invalid           Critical      1      1 
    3421 6421 Valid              Active       1      2
    1222 6421 Valid           Chain           1      3
    5643 2243 Invalid           Critical      2      1
    2243 3491 Valid           Active          2      2
    5643 3491 Valid           Chain           2      3

always step 1 and step 3 are both equal on PartID C
always step 1 and step 2 are both equal on PartID x from step 1 equal to PartID C from step 2 .
so How to do Expected result above by update statement to group Id ?
Check if it works for you. Please, notice this code is meant to work with groups containing no more than 3 steps each.
;WITH CTE_Replacement
AS
(
    --Creates the groups by selecting only the rows with step 1
    SELECT PartIDC, 
        PartIDX, 
        FlagStatus, 
        HasReplacement, 
        ROW_NUMBER() OVER(ORDER BY PartIDC) AS groupId, 
        step
    FROM #Replacement
    WHERE step = 1

    UNION ALL

    --Gets steps 2 that are lelated to the groups with step 1 from the query above
    SELECT R.PartIDC, 
        R.PartIDX, 
        R.FlagStatus, 
        R.HasReplacement, 
        CTE.groupId, --Uses the group created before
        R.step
    FROM #Replacement R
        INNER JOIN CTE_Replacement CTE ON R.PartIDC = CTE.PartIDX
    WHERE R.step = 2
) 
--Adds the last step of each goup
SELECT R.PartIDC, 
        R.PartIDX, 
        R.FlagStatus, 
        R.HasReplacement, 
        CTE.groupId, 
        R.step 
FROM #Replacement R
    LEFT JOIN CTE_Replacement CTE ON R.PartIDX = CTE.PartIDX
ORDER BY CTE.groupId, CTE.step;