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

Rijwan Ansari · Answer

Try this way create table #test ( partNumber nvarchar(200), serialNumber int ) insert into #test(partNumber,serialNumber) values ( 'nna5' ,9), ( 'nfggg' ,20), ( 'ddfg' ,10), ( 'llm' ,(SELECT MAX( serialNumber)+1 FROM #test test)), ( '109' ,(SELECT MAX( serialNumber)+1 FROM #test test)), ( '8654' ,(SELECT MAX( serialNumber)+1 FROM #test test)), ( '1234' ,30

Priyanka K S · Answer

UPDATE #test SET serialNumber = max (serialNumber)+1 Where serialNumber IS NULL

Nitin Sontakke · Answer

Try this: declare @ max integer ; declare @nullCount integer ; select @nullCount = count (1) from #test where [serialNumber] is null while @nullCount != 0 begin select @ max = isnull ( max ([serialNumber]), 0) + 1 from #test where [serialNumber] is not null ; update top (1) #test set [serialNumber] = @ max where [serialNumber] is null select @nullCount = count (1) from #test where [serialNumber] is null end select * from #test

Priyanka K S · Answer

@Nitin Thanks. My bad, you are right. I tried this earlier so I wrote it directly. Below piece of code work for sure. I tested. declare @i int = ( select max ( serialNumber ) from #test where serialNumber is not null ) UPDATE #test SET serialNumber = @i , @i = @i + 1 Where serialNumber IS NULL

Nitin Sontakke · Answer

@Priyanka, Not sure if you tested the answer you provided. I seem to be getting the following error: Msg 157, Level 15, State 1, Line 16 An aggregate may not appear in the set list of an UPDATE statement.