Update a column with the max trackingNumber and increment by 1-CodePudding

I have a query that I am building in SQL Server that is looking for the max tracking Number and adding 1 to the current value for each record with the same lotno in the job where tracking number is zero. The query that I have is updating all the records in this job with the same number. I need each record in this job to have a different number.

Here is the query I have:

UPDATE TEST.DBO.J_CordToolARCCutSheetPlanning 
SET TrackingNumber = (SELECT (MAX(trackingNumber)   1) AS track 
                      FROM TEST.DBO.J_CordToolARCCutSheetPlanning j)
WHERE JobNumber = '1234'

Sample:

Job   LotNO        SerialNo TrackingNumber
------------------------------------------
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

In this example the max tracking Number is 5.

I need it to update each record by the max (5) plus one.

This is what I need it to do

Job   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

CodePudding user response：

You can use an updatable CTE to accomplish a unique row number as follows below using dense_rank to generate your required values.

Note: as it stands your description is inconsistent with your desired results. Your description says a unique row number whereas your desired results show a common tracking number per lot number.

Also you would be much better off using an automatic sequence (identity column or sequence) rather than manually calculating this.

with cte as (
    -- Choose a suitable ordering column
    select
        -- Sample data assigns the same number for the entire lot
        dense_rank() over (order by LotNo) rn
        -- If a unique number per row is required (as written) use row_number
        --row_number() over (order by LotNo, SerialNo) rn
    from dbo.J_CordToolARCCutSheetPlanning
    where JobNumber = '1234'
    and TrackingNumber = 0
)
update cte set TrackingNumber = dr   (select max(trackingNumber)   1 from dbo.J_CordToolARCCutSheetPlanning);

CodePudding user response：

Use the DENSE_RANK window function to rank the row in LotNO and add the result with max trackingNumber

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

demo in db<>fiddle