DBCC校验异常：执行BulkInvoice更新脚本后仍有错误记录求助

DBCC规则要求cc_bulkinvoice表的TotalTransactionAmount（总交易金额）必须等于其所有关联cc_bulkinvoiceitem子项的金额之和。

错误记录查询SQL #

SELECT Count (*)
FROM   (SELECT cc_bulkinvoice.id
FROM   cc_bulkinvoice
WHERE  cc_bulkinvoice.status NOT IN ( 17, 23 )
AND (SELECT ( CASE
WHEN Sum (bii.amount) IS NOT NULL THEN Sum
(bii.amount)
ELSE 0
END )
FROM   cc_bulkinvoiceitem bii
WHERE  bii.bulkinvoiceid = cc_bulkinvoice.id
AND bii.retired = 0) <>
cc_bulkinvoice.totaltransactionamount) a

执行的更新脚本 #

USE ClaimCenter;

begin transaction
update cc_bulkinvoice
set cc_bulkinvoice.TotalTransactionAmount = t2.sumAmt
from cc_bulkinvoice b  inner join (
select bii.BulkInvoiceID,sum(bii.Amount) as sumAmt from cc_bulkinvoiceitem bii
where bii.Retired = 0
group by bii.BulkInvoiceID
) as t2 on t2.BulkInvoiceID = b.ID
where b.id in (26841,26849,26850,26856,13036,13037,2003,2214)

      
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;       

执行上述更新脚本后，错误记录查询语句仍返回错误记录，以下是脚本存在的核心问题及修正方案：

问题分析 #

1. 未处理无有效子项的主表记录
   查询语句中通过CASE WHEN将无有效子项（Sum(bii.amount)为NULL）的情况计算为0，但更新脚本使用INNER JOIN关联子项汇总结果，导致没有有效子项的cc_bulkinvoice记录无法被匹配，也就不会被更新。如果这类记录的TotalTransactionAmount不为0，就会持续被判定为错误。

2. 更新范围仅覆盖固定ID，未覆盖全部错误记录
   更新脚本指定了固定的ID列表，但查询语句返回的错误记录可能不止这些ID，导致部分错误记录未被处理。

3. 未过滤无需处理的状态记录
   查询语句排除了status IN (17,23)的记录，但更新脚本未添加该过滤条件，可能更新了不需要处理的记录（不过这不是导致错误残留的直接原因）。

修正后的更新脚本 #

USE ClaimCenter;

BEGIN TRANSACTION;

-- 仅更新符合查询条件的错误记录，同时处理无有效子项的情况
UPDATE b
SET b.TotalTransactionAmount = ISNULL(t2.sumAmt, 0)
FROM cc_bulkinvoice b
LEFT JOIN (
    SELECT bii.BulkInvoiceID, SUM(bii.Amount) AS sumAmt
    FROM cc_bulkinvoiceitem bii
    WHERE bii.Retired = 0
    GROUP BY bii.BulkInvoiceID
) AS t2 ON t2.BulkInvoiceID = b.ID
WHERE b.status NOT IN (17, 23)
AND EXISTS (
    -- 关联错误查询逻辑，确保只更新真正的错误记录
    SELECT 1
    FROM (
        SELECT cc_bulkinvoice.id
        FROM cc_bulkinvoice
        WHERE cc_bulkinvoice.status NOT IN (17, 23)
        AND (SELECT CASE WHEN SUM(bii.amount) IS NOT NULL THEN SUM(bii.amount) ELSE 0 END
             FROM cc_bulkinvoiceitem bii
             WHERE bii.bulkinvoiceid = cc_bulkinvoice.id AND bii.retired = 0) <> cc_bulkinvoice.totaltransactionamount
    ) AS error_records
    WHERE error_records.id = b.ID
);

-- 先执行错误查询验证更新结果，确认无误后再提交
-- ROLLBACK TRANSACTION;
COMMIT TRANSACTION;

验证步骤 #

执行更新脚本后，重新运行错误记录查询语句，若返回0则说明所有错误已修复。

内容的提问来源于stack exchange，提问作者00000000000