Update one column based on count of records of grouped value in another column Given a table 

CREATE TABLE TABLE_ITEMS (
    serial_num int null,
    item_group int null
 );

INSERT INTO TABLE_ITEMS
VALUES 
 (0,1), (1,1), (2,1), (3,1), (4,1),
 (0,2), (1,2), (2,2), (3,2), (4,2), (5,2), 
 (0,3), (1,3), (2,3);


and so on... 

I need to know how to run a query which will update the rows with 0 as the serial_num to be the next number in the serial_num sequence for the given item_group

The result will look like this: 

   those were the zeroes
   ↓
(**5**,1) (1,1) (2,1) (3,1) (4,1) 
(**6**,2) (1,2) (2,2) (3,2) (4,2) (5,2) 
(**3**,3) (1,3) (2,3) 


I want to update an item in serial_num=0 to be the last serial_num plus 1 (if the last one was 70, it needs to be 71).

How can I do it?
CREATE TABLE TABLE_ITEMS (
    serial_num int null,
    item_group int null
 )
 GO

INSERT INTO TABLE_ITEMS
VALUES 
(0,1), (1,1), (2,1),(3,1) ,(4,1) ,(0,2) ,(1,2), (2,2), (3,2), (4,2), (5,2), (0,3) ,(1,3) ,(2,3)
GO

SELECT 
    Item_group, 
    ROW_NUMBER() OVER (PARTITION BY item_group order by item_group) AS AddNum 
INTO #NewSerials
from TABLE_ITEMS 
WHERE serial_num &lt;&gt; 0

SELECT * FROM TABLE_ITEMS

;WITH CTE AS 
(SELECT MAX(addnum) as addnum, item_group from #NewSerials group by item_group)
UPDATE ti
    SET ti.serial_num = CTE.AddNum
FROM TABLE_ITEMS ti
JOIN CTE ON CTE.item_group = ti.item_group
WHERE ti.serial_num = 0

SELECT * FROM TABLE_ITEMS

5   1
1   1
2   1
3   1
4   1
6   2
1   2
2   2
3   2
4   2
5   2
3   3
1   3
2   3