How to Renumber Set/Subset of Table Based on Numbering of A Second Table

Hello everyone! I hope this video has helped solve your questions and issues. This video is shared because a solution has been found for the question/problem. I create videos for questions that have solutions. If you have any other issues, feel free to reach out to me on Instagram:   / ky.emrah  

Below, you can find the text related to the question/problem. In the video, the question will be presented first, followed by the answers. If the video moves too fast, feel free to pause and review the answers. If you need more detailed information, you can find the necessary sources and links at the bottom of this description. I hope this video has been helpful, and even if it doesn't directly solve your problem, it will guide you to the source of the solution. I'd appreciate it if you like the video and subscribe to my channel!How to Renumber Set/Subset of Table Based on Numbering of A Second Table

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);

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]

-- 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 tSource of the question:
https://stackoverflow.com/questions/7...

Question and source license information:
https://meta.stackexchange.com/help/l...
https://stackoverflow.com/