How to update group no incremental by 1 based on every group from 1 to 3 ? RSS

• • Reply
  ahmedbarbary

  Member

  41 Points

  374 Posts

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

  Sep 13, 2020 12:54 AM|ahmedbarbary|LINK

  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 ?

• • Reply
  mgebhard

  All-Star

  53641 Points

  24001 Posts

  Re: How to update group no incremental by 1 based on every group from 1 to 3 ?

  Sep 13, 2020 11:53 AM|mgebhard|LINK

  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.