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 |