Updating IDs within a resulting table?

Question

I have a table and I want to update the NewID value based on matching ID's Example:

I have this table:

ID ReplyID NewID ClientID

99 70 NULL 138
100 71 NULL 138
101 95 NULL 108
137 111 NULL 31
138 111 NULL 31
139 111 NULL 31

Assuming the above data, I want to update the NewID Column based on the number of matching IDs in the ReplyID column. So the resulting DS should look like:

ID ReplyID NewID ClientID

99 70 1 138
100 71 1 138
101 95 1 108
137 111 1 31
138 111 2 31
139 111 3 31

where the NewID Increments if the ReplyID is the same. Any ideas on how to do this?

Answer 1

Hi @Jim Seidel ,

Please refer below:

declare @temp table   
(  
ID int,  
ReplyID int,  
[NewID] int,  
ClientID int  
)  
  
insert into @temp values  
(99,70,NULL,138),  
(100,71,NULL,138),  
(101,95,NULL,108),  
(137,111,NULL,31),  
(138,111,NULL,31),  
(139,111,NULL,31)  
  
select ID,ReplyID,[NewID]=ROW_NUMBER() OVER(PARTITION BY ReplyID ORDER BY ID) ,ClientID  
from @temp  

Output:

ID	ReplyID	NewID	ClientID  
99	70	1	138  
100	71	1	138  
101	95	1	108  
137	111	1	31  
138	111	2	31  
139	111	3	31  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 2

Hi @Jim Seidel ,

You could also refer below update statement.

create table temp   
(  
ID int,  
ReplyID int,  
[NewID] int,  
ClientID int  
)  
  
insert into temp values  
(99,70,NULL,138),  
(100,71,NULL,138),  
(101,95,NULL,108),  
(137,111,NULL,31),  
(138,111,NULL,31),  
(139,111,NULL,31)  
  
;With cte  As  
(  
SELECT ID,ReplyID,  
ROW_NUMBER() OVER(PARTITION BY ReplyID ORDER BY ID) AS RN  
FROM temp  
)  
UPDATE temp SET [NewID] = RN  
FROM temp a  
INNER JOIN cte b ON a.ID=b.id and a.ReplyID=b.ReplyID   
  
select * from temp  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table