SQL Server -更新丢失的历史记录

Question

我正在使用一个大约30,000个记录的合同历史表，这里用#CONTR表示。表中的原始合同行号OCL列不完整。我需要在适当的地方更新OCL (参见关于INSERT语句的说明)。

对于新合同行，OCL应为NULL。一些正确的例子：CONT&CL = 1415&1、1415&2、1415&3、1415&16、2025&1、2025&2和2025&13)。没有需要更新的示例。

第一次续签合同时，OCL应等于PCL。一些正确的例子：CONT&CL = 1415&20和2015&16。一些需要更新的例子：CONT&CL = 1415&4,1415&5,1415&6,2025&3和2025&4。

在第二次续订及后续续订时，OCL应始终等于其来源的合同项。一些正确的示例：CONT&CL = 1415&10、1415&11、2015&9和2025&10。需要更新的一些示例：CONT&CL = 1415&6、1415&7、2025&5和2025&6

CREATE TABLE #CONTR
(
CONT    INT ,   --  PK Col1, Contract Number
OCL     INT ,   --  Original Contract Line Number
CL      INT ,   --  PK Col2, Contract Line Number
PCL     INT ,   --  Previous Contract Line Number
NCL     INT     --  Next Contract Line Number
)

INSERT INTO #CONTR VALUES (1415,NULL,1,0,4)
INSERT INTO #CONTR VALUES (1415,NULL,2,0,5) 
INSERT INTO #CONTR VALUES (1415,NULL,3,0,6) 
INSERT INTO #CONTR VALUES (1415,NULL,4,1,7)     -- OCL needs to be updated to 1
INSERT INTO #CONTR VALUES (1415,NULL,5,2,8)     -- OCL needs to be updated to 2
INSERT INTO #CONTR VALUES (1415,NULL,6,3,9)     -- OCL needs to be updated to 3
INSERT INTO #CONTR VALUES (1415,NULL,7,4,10)    -- OCL needs to be updated to 1
INSERT INTO #CONTR VALUES (1415,NULL,8,5,11)    -- OCL needs to be updated to 2
INSERT INTO #CONTR VALUES (1415,NULL,9,6,12)    -- OCL needs to be updated to 3
INSERT INTO #CONTR VALUES (1415,1,10,7,13)  
INSERT INTO #CONTR VALUES (1415,2,11,8,14)  
INSERT INTO #CONTR VALUES (1415,3,12,9,15)  
INSERT INTO #CONTR VALUES (1415,1,13,10,17) 
INSERT INTO #CONTR VALUES (1415,2,14,11,18) 
INSERT INTO #CONTR VALUES (1415,3,15,12,19) 
INSERT INTO #CONTR VALUES (1415,NULL,16,0,20)   
INSERT INTO #CONTR VALUES (1415,1,17,13,21) 
INSERT INTO #CONTR VALUES (1415,2,18,14,22) 
INSERT INTO #CONTR VALUES (1415,3,19,15,23) 
INSERT INTO #CONTR VALUES (1415,16,20,16,24)    
INSERT INTO #CONTR VALUES (2025,NULL,1,0,5) 
INSERT INTO #CONTR VALUES (2025,NULL,2,0,6) 
INSERT INTO #CONTR VALUES (2025,NULL,3,1,5)     -- OCL needs to be updated to 1
INSERT INTO #CONTR VALUES (2025,NULL,4,2,6)     -- OCL needs to be updated to 2
INSERT INTO #CONTR VALUES (2025,NULL,5,3,7)     -- OCL needs to be updated to 1
INSERT INTO #CONTR VALUES (2025,NULL,6,4,8)     -- OCL needs to be updated to 2
INSERT INTO #CONTR VALUES (2025,NULL,7,5,9)     -- OCL needs to be updated to 1
INSERT INTO #CONTR VALUES (2025,NULL,8,6,10)    -- OCL needs to be updated to 2
INSERT INTO #CONTR VALUES (2025,1,9,7,11)   
INSERT INTO #CONTR VALUES (2025,2,10,8,12)  
INSERT INTO #CONTR VALUES (2025,1,11,9,14)  
INSERT INTO #CONTR VALUES (2025,2,12,10,15) 
INSERT INTO #CONTR VALUES (2025,NULL,13,0,16)   
INSERT INTO #CONTR VALUES (2025,1,14,11,17) 
INSERT INTO #CONTR VALUES (2025,2,15,12,18) 
INSERT INTO #CONTR VALUES (2025,13,16,13,19)

我尝试了下面的UPDATE，但当合同行号不是新的时，它们不会纠正OCL错误为空的所有实例，并且在实际数据中，OCL错误为空的历史可能比我们在此示例( CONT = 2025 )中的情况更长。

UPDATE #CONTR
SET    OCL = C1.PCL
FROM #CONTR C1
     JOIN 
     (SELECT * FROM #CONTR WHERE PCL = 0) C2
     ON C1.CONT = C2.CONT
     AND C1.PCL = C2.CL
     WHERE C1.OCL IS NULL AND C1.PCL != 0

UPDATE #CONTR
SET    OCL = C2.PCL
FROM #CONTR C1
     JOIN 
     (SELECT * FROM #CONTR WHERE PCL != 0 AND PCL = OCL) C2
     ON C1.CONT = C2.CONT
     AND C1.PCL = C2.CL
     WHERE C1.OCL IS NULL AND C1.PCL != 0

Answer 1

尝试：

 UPDATE
    c1
SET
    c1.OCL=c2.PCL
FROM
    #CONTR c1
INNER JOIN
    #CONTR c2
ON
    c1.CONT = c2.CONT where C1.OCL IS NULL AND C1.PCL != 0 and c2.PCL=0