locked
How to update group no incremental by 1 based on every group from 1 to 3 ? RRS feed

  • Question

  • User696604810 posted

    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	GroupNo	Steps
    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 PartIDC

    always step 1 and step 2 are both equal on PartIDx from step 1 equal to PartIDC from step 2 .

    so How to do Expected result above by update statement to groupId ?

    Sunday, September 13, 2020 12:54 AM

All replies

  • User475983607 posted

    The design looks a bit iffy.  I assume you need to sort by steps and create a self join between PartIDC and PartIDX? 

    As far as I can tell your expected results do not match the written requirements.

    Sunday, September 13, 2020 11:53 AM