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

ahmed elbarbary

6y918

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

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 ?

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

In general (99.99999% ) of the times and dealling with todays Big Data, I totally disagree with LOOPS in T-SQL scripts with SQL server

Hi Ahmed Elbarbary,

After enough tries, I have done with following way:

create table #Replacement_tmp (ID int identity, PartIDC INT, PartIDX INT)
insert into #Replacement_tmp (PartIDC, PartIDX) select PartIDC, PartIDX from #Replacement where step = 1
declare @i as int
declare @total as int
declare @PartIDC_ as int
declare @PartIDX_ as int
set @i = 1
select @total = count(Id) from #Replacement_tmp
while @i <= @total
begin
select @PartIDC_ = PartIDC, @PartIDX_ = PartIDX from #Replacement_tmp where Id = @i
update #Replacement set groupId = @i where PartIDC = @PartIDC_
update #Replacement set groupId = @i where PartIDC = @PartIDX_
set @i = @i + 1
end
drop table #Replacement_tmp

Hope, this will help you!