I have a table of numbered operations. I have a second table with materials tied to those operations. I need to “remove” two of the operations and reassign the materials to the next operation in line.

Here is my sample data:

DROP TABLE IF EXISTS [operations];
CREATE TABLE [operations] (
    [WorkOrder] varchar(50),
    [LotID] int,
    [OprSeq]    int,
    [Operation] varchar(20),
    [Old_Seq] int,
    [New_Seq] int
);
INSERT INTO [operations] VALUES
('RTA123456', 0, 10, 'PZZ', 10, 0),
('RTA123456', 0, 20, 'LA',  20, 0),
('RTA123456', 0, 30, 'LA',  30, 0),
('RTA123456', 0, 40, 'PZZ', 40, 0),
('RTA123456', 0, 50, 'PO',  50, 0),
('RTA123456', 0, 60, 'SHP', 60, 0);

DROP TABLE IF EXISTS [materials];
CREATE TABLE [materials] (
    [WorkOrder] varchar(50),
    [LotID] int,
    [PartID] varchar(20),
    [OpSeqNo]   int,
    [Old_OpSeq] int,
    [New_OpSeq] int
);
INSERT INTO [materials] VALUES
('RTA123456', 0, 'LA2',   10, 10, 0),
('RTA123456', 0, 'LA-P',  10, 10, 0),
('RTA123456', 0, '12345', 10, 10, 0),
('RTA123456', 0, '23456', 10, 10, 0),
('RTA123456', 0, '34567', 10, 10, 0),
('RTA123456', 0, '45678', 40, 40, 0),
('RTA123456', 0, '56789', 40, 40, 0),
('RTA123456', 0, '67890', 40, 40, 0);

I created a CTE that will renumber the operations:

-- Renumbers Operations
WITH NewSeqNo AS (
    SELECT *,
        (ROW_NUMBER() OVER (PARTITION BY [WorkOrder], [LotID] ORDER BY [Old_Seq])) *10 AS [New]
      FROM [operations]
WHERE [Operation] NOT IN ('PZZ', 'SHP')
)
    UPDATE [operations]
       SET [operations].[New_Seq] = seq.[New]
      FROM [operations] o
INNER JOIN [NewSeqNo] seq
        ON seq.[WorkOrder] = o.[WorkOrder]
       AND seq.[Old_Seq] = o.[Old_Seq]
       AND seq.[LotID] = o.[LotID]

So I’d look at Material table for WorkOrder RTA123456, and PartID LA2 that has Materials.OpSeqNo=10. Then look at the Operation table, and the next Operation.SeqNo after Materials.OpSeqNo=10 is Operation.SeqNo = 20. The Operation.NewSeq for SeqNo#20 is 10.
I can then hop down to OpSeqNo = 40 and do the same thing. If I look at the next SeqNo, SeqNo#50 is NewSeq#30.

I managed an UPDATE that seems to work

UPDATE [materials]
SET [New_OpSeq] = (
    SELECT TOP(1) [New_Seq] 
      FROM [operations] 
      WHERE [WorkOrder] = [WorkOrder] 
        AND [LotID] = [LotID]
        AND [Old_Seq] > [Old_OpSeq]
        AND [New_Seq] > 0
    ORDER BY [Old_Seq] )
  WHERE [New_OpSeq] = 0

BUT, if materials are tied to an operation that doesn’t have any operations after it I get an error that it can’t insert NULL.
The real issue is that this works for my sample data, but does NOT work on my larger set. Instead of 10 and 30 for the New_OpSeq it gives me 20 and 40. It seems to add 10 to whatever the result is. I have NO idea why!!
The sample data is exactly the same (Except for the WO#). I even renamed the Seq fields to match just to rule that out.

I created a SQL Fiddle to show my work, and that it works. I am looking for ANY ideas on why the same thing won’t work on my larger dataset!