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

ahmed elbarbary

5y1.1k

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

5 Replies

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

Priyanka K S5y ago
@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 Sontakke5y ago
@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.
Rijwan Ansari5y ago
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 S5y ago
1. UPDATE #test SET serialNumber = max(serialNumber)+1 Where serialNumber IS NULL

Nitin Sontakke5y ago

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