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

Transact-SQL
,
1

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
2

select partnumber , row_number() over( order by (select 1)) + maxno
from #test a
cross apply (select maxno = max(serialnumber) from #test) b
where a.serialnumber is null

for update

with cte as
(
select partnumber , maxno=row_number() over( order by (select 1)) + maxno
from #test a
cross apply (select maxno = max(serialnumber) from #test) b
where a.serialnumber is null
)
update c
set c.serialnumber = cte.maxno
from cte
join #test c on cte.partnumber = c.partnumber

3

A lot of people don't know it but you CAN update a CTE and it will update the underlying table without the extra join in the outer SELECT. They following references the table twice instead of three times saving a fair bit on READs with our without indexes.

 WITH cte AS
(
 SELECT  NewSN = CASE WHEN serialNumber IS NULL THEN ROW_NUMBER() OVER (ORDER BY @@SPID) ELSE 0 END
               + (SELECT MAX(serialNumber) FROM #test WHERE serialNumber > '')
        ,serialNumber
   FROM #test
  WHERE serialNumber IS NULL
)
 UPDATE cte
    SET serialNumber = NewSN
  WHERE serialNumber IS NULL
;
1 Like
4

Thanks for updating.

5

thanks