基于多列唯一值集合更新MSSQL列的问题排查

我有两张关联表：AC_INSTALLMENT_TRK（简称tableA）和AC_BORDRO_ALLOCATION_TRK（简称tableB），需要将tableA的value1列更新为tableB对应的value2列值。但执行现有MSSQL查询后，所有行的value1都被替换成了tableB第一行的value2值，无法实现逐行对应更新的效果。

原查询语句 #

WITH DataSet as (
   select  distinct tabA.id , tabB.MappId  
from AC_BORDRO_ALLOCATION_TRK tabB JOIN AC_INSTALLMENT ai ON tabB.INSTALLMENT_ID =ai.ID 
JOIN AC_CREDIT_ALLOCATION_IN_BORDRO_TRK cb ON tabB.ID =cb.BORDRO_ALLOCATION_ID  
JOIN AC_INSTALLMENT_TRK tabA ON ai.id=tabA.id
JOIN AC_INSTALLMENT bordro_installment ON tabB.MappId =bordro_installment.id  
JOIN AC_INSTALLMENT_STATUS ais on ai.LAST_INSTALLMENT_STATUS_ID  = ais.ID 
JOIN AC_ENTRY ae on ae.REFERENCE_ID  = tabB.MappId 
where bordro_installment.INSTALLMENT_STATUS = 3 and abs(tabB.TRANSACTION_AMOUNT) 
!= abs(tabA.PAID_AMOUNT_IN_BODRO)  
and tabB.REMAINING_AMOUNT_TO_BE_PAID  = 0 and tabB .REFUND_BORDRO =0
group by tabA.id,tabB.MappId,bordro_installment.COLLECTION_METHOD_ID  ,
tabA.PAID_AMOUNT_IN_BODRO, tabB.TRANSACTION_AMOUNT, tabB.REMAINING_AMOUNT_TO_BE_PAID ,
ae.REFERENCE_ID ,ai.STORED_INST_AMOUNT having abs(sum(cb.AMOUNT))!=abs(tabA.PAID_AMOUNT_IN_BODRO)

    ) 
    update tableA set tableA.value1  = tableB.value2
    from DataSet ob  join AC_INSTALLMENT_TRK tableA on tableA.id =   ob.id  
    join AC_BORDRO_ALLOCATION_TRK tableB on tableB.INSTALLMENT_ID  = tableA.id  
    where  tableB.MappId  = ob.Mappid and tableA.id = ob.id 

问题排查 #

核心问题在于更新阶段的表关联逻辑未保证tableA与tableB的唯一对应关系：

• DataSet仅保留了tabA.id和tabB.MappId，但更新时仅通过tableB.INSTALLMENT_ID = tableA.id关联，当一个tableA.id对应多个tableB行时，SQL会随机选取其中一行的值（通常是第一行）覆盖所有匹配行。
• 虽然DataSet中用了distinct和group by去重，但更新时的关联条件未将DataSet的MappId与tableB的MappId做强绑定，导致关联逻辑失效。

修复后的查询 #

直接在DataSet中获取需要更新的value2值，确保更新阶段的关联能唯一匹配对应行：

WITH DataSet as (
   select  tabA.id as tableA_id, tabB.value2
from AC_BORDRO_ALLOCATION_TRK tabB 
JOIN AC_INSTALLMENT ai ON tabB.INSTALLMENT_ID = ai.ID 
JOIN AC_CREDIT_ALLOCATION_IN_BORDRO_TRK cb ON tabB.ID = cb.BORDRO_ALLOCATION_ID  
JOIN AC_INSTALLMENT_TRK tabA ON ai.id = tabA.id
JOIN AC_INSTALLMENT bordro_installment ON tabB.MappId = bordro_installment.id  
JOIN AC_INSTALLMENT_STATUS ais on ai.LAST_INSTALLMENT_STATUS_ID = ais.ID 
JOIN AC_ENTRY ae on ae.REFERENCE_ID = tabB.MappId 
where bordro_installment.INSTALLMENT_STATUS = 3 
  and abs(tabB.TRANSACTION_AMOUNT) != abs(tabA.PAID_AMOUNT_IN_BODRO)  
  and tabB.REMAINING_AMOUNT_TO_BE_PAID = 0 
  and tabB.REFUND_BORDRO = 0
group by tabA.id, tabB.MappId, tabB.value2, bordro_installment.COLLECTION_METHOD_ID,
         tabA.PAID_AMOUNT_IN_BODRO, tabB.TRANSACTION_AMOUNT, tabB.REMAINING_AMOUNT_TO_BE_PAID,
         ae.REFERENCE_ID, ai.STORED_INST_AMOUNT 
having abs(sum(cb.AMOUNT)) != abs(tabA.PAID_AMOUNT_IN_BODRO)
) 
update AC_INSTALLMENT_TRK 
set value1 = ds.value2
from AC_INSTALLMENT_TRK tableA
join DataSet ds on tableA.id = ds.tableA_id;

关键优化点 #

• 在DataSet中直接关联获取tabB.value2，避免更新阶段再次关联tableB时出现一对多的情况。
• 更新时直接用DataSet与tableA做唯一关联（tableA.id = ds.tableA_id），确保每行只被匹配一次，获取正确的对应值。
• 若DataSet中仍存在同一个tableA.id对应多个value2的情况，需先排查数据逻辑，确保每个tableA行有且仅有一个对应的tableB行，或通过聚合函数（如MAX(tabB.value2)）确定唯一更新值。

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