SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table J_CordToolARCCutSheetPlanning(JobNumber int, LotNO varchar(20), SerialNo int, TrackingNumber int);

insert into J_CordToolARCCutSheetPlanning values
(1234 , '199111 -001' , 40001    ,0),
(1234 , '199111 -001' , 40033    ,0),
(1234 , '199111 -004' , 40050   , 0),
(1234 , '199111 -004' , 40051    ,0),
(1234 , '199111 -008' , 40111    ,5),
(1234 , '199111 -008' , 40112    ,5);
6 rows affected

UPDATE
    J_CordToolARCCutSheetPlanning 
SET
    J_CordToolARCCutSheetPlanning.TrackingNumber = CASE WHEN t.trackingNumber != t.max_trackingNumber THEN t.max_trackingNumber + t.seq ELSE t.trackingNumber END
FROM
  (SELECT *,
       DENSE_RANK() over(ORDER BY t.LotNO) AS seq,
       (SELECT MAX(trackingNumber) FROM J_CordToolARCCutSheetPlanning) AS max_trackingNumber
  FROM J_CordToolARCCutSheetPlanning t
  WHERE JobNumber = '1234') t
WHERE J_CordToolARCCutSheetPlanning.SerialNo = t.SerialNo
6 rows affected

select * from J_CordToolARCCutSheetPlanning


JobNumber
LotNO
SerialNo
TrackingNumber


1234
199111 -001
40001
6

1234
199111 -001
40033
6

1234
199111 -004
40050
7

1234
199111 -004
40051
7

1234
199111 -008
40111
5

1234
199111 -008
40112
5

JobNumber	LotNO	SerialNo	TrackingNumber
1234	199111 -001	40001	6
1234	199111 -001	40033	6
1234	199111 -004	40050	7
1234	199111 -004	40051	7
1234	199111 -008	40111	5
1234	199111 -008	40112	5