How to update colum serial where is null by max+1 ?

Question

I work on SQL server 2012 I face issue i can't update column serial by max + 1
where is have null value on on serial column
partnumber is unique on table
so i need to get max number from serial and increment it by 1
then assign it to null on serial column

meaning any partnumber is unique and have null on serial
must have unique number on serial by max number on serial +1
for every part number

create table #test
(
partNumber  nvarchar(200),
serialNumber  int
)
insert into #test(partNumber,serialNumber)
values
('nna5',9),
('nfggg',20),
('ddfg',10),
('llm',NULL),
('109',NULL),
('8654',NULL),
('1234',30)

expected result

partNumber serialNumber
llm 31
109 32
8654 33

Answer 1

Try:

     ;WITH cte  
     AS(SELECT *,ROW_NUMBER() OVER(ORDER BY partNumber) rr FROM #test WHERE serialNumber IS NULL)  
      
     UPDATE c  
     SET serialNumber=(SELECT MAX(serialNumber) FROM #test)+rr  
     FROM cte c  

The final result is slightly different from what you expect because of sorting by partNumber.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Hey @ahmed salah ,
You can achieve this by the below process:

1. Initially insert the data as is meaning NULL values be inserted as NULL itself
2. Then take a count of records where that column is null
3. then using while loop iteratively update the rows for that column as
   Select max(serialNumber) +1 from #test

This should help you achieve your results but not sure whether this is the most clean way to do it