折旧调整单MERGE语句试图多次更新或删除同一行

报错类似截图：

解决步骤：

1. 取出报错日志记录的sql语句，这种错误是总部插件执行语句报错，虽已定位数据问题无异于大海捞针，除了通过语句看数据毫无它法，sql语句如下：
   

MERGE INTO
t_fa_Balance B
 Using(SELECT M.FAcctPolicyID, M.FYear, M.FPeriod, C.FAssetID, C.FISNEWREC,
  CASE  WHEN (ISNULL(FISAFFECTNEXTPERIOD, 0) = 0 AND (E.FSHOULDDEPR <> E.FDEPR))
   THEN E.FDEPR ELSE E.FCARDDEPR END fshoulddepr, E.FSHOULDDEPR fshoulddeprbak, E.FDepr, E.FCARDDEPR,
    E.FDeprRate, E.FAssetCurStatus, E.FDeprMethod, AC.Fperiodcount, E.FPeriodWorkLoad FROM t_Fa_Depradjust M
  INNER JOIN t_Fa_DepradjustEntry E ON M.FID = E.FID INNER JOIN t_fa_card C ON C.FAlterID = E.FAlterID
  INNER JOIN T_FA_ACCTPOLICY P ON P.FACCTPOLICYID = M.FACCTPOLICYID
   INNER JOIN T_BD_ACCOUNTCALENDAR AC ON P.FACCTCALENDARID = AC.FID
   INNER JOIN T_FA_FINANCE F ON (F.FALTERID = E.FALTERID AND F.FACCTPOLICYID = M.FACCTPOLICYID)
   WHERE ((E.FID IN (100059) AND (E.FDepr > 0)) AND M.FOwnerOrgID = C.FOwnerOrgID)) T ON
   ((((B.FAssetID = T.FAssetID AND B.FYear = T.FYear) AND B.FPeriod = T.FPeriod) AND B.FAcctPolicyID = T.FAcctPolicyID)) WHEN
   MATCHED  
   THEN UPDATE SET B.FShouldDepr = T.FSHOULDDEPR, B.FShouldDeprBak = CASE ISNULL(B.FShouldDeprBak, 0)
    WHEN 0 THEN T.FShouldDeprBak ELSE B.FShouldDeprBak END, B.FDepr = T.FDepr, B.FDeprRate =
  T.FDeprRate, B.FDeprPeriods = CASE  WHEN T.FDeprMethod = 4 THEN B.FDeprPeriods
  ELSE (B.FDeprPeriods + 1) END, B.FCurYearDepr = (B.FCurYearDepr + T.FDepr), B.FIsNextFirstPeriod =
  CASE ((B.FDeprPeriods + 1) % T.Fperiodcount) WHEN 0 THEN '1' ELSE '0' END, B.FYtdDeprPeriod =
  CASE CASE ((B.FDeprPeriods + 1) % T.Fperiodcount) WHEN 0 THEN '1' ELSE '0' END WHEN '1'
  THEN (B.FDeprPeriods + 1) ELSE B.FYtdDeprPeriod END, B.FDeprWorkLoad = (B.FDeprWorkLoad + T.FPeriodWorkLoad), B.FACCUMDEPRDEC = CASE  WHEN (T.FAssetCurStatus IN ('3', '4') AND T.FISNEWREC = '0') THEN (B.FACCUMDEPRDEC + (T.FDEPR - T.FCARDDEPR)) ELSE B.FACCUMDEPRDEC END;

这里的加粗ID是折旧调整单对应的FID，请自行替换

2.从语句中分析可以看出，根源就是MERGE INTO目标表，源表存在重复数据into目标表报错，举个例子：

a表有id 001 的数据两条，b表有id 001 的数据一条，此时a为源表（也就是using块里面查询结果）b为目标表，从a到b有两次插入更新同一条就会出现此类错误抛出

3.取出源表结果集去分析数据，using块里如下：

SELECT M.FAcctPolicyID, M.FYear, M.FPeriod, C.FAssetID, C.FISNEWREC,
  CASE  WHEN (ISNULL(FISAFFECTNEXTPERIOD, 0) = 0 AND (E.FSHOULDDEPR <> E.FDEPR))
   THEN E.FDEPR ELSE E.FCARDDEPR END fshoulddepr, E.FSHOULDDEPR fshoulddeprbak, E.FDepr, E.FCARDDEPR,
    E.FDeprRate, E.FAssetCurStatus, E.FDeprMethod, AC.Fperiodcount, E.FPeriodWorkLoad FROM t_Fa_Depradjust M
  INNER JOIN t_Fa_DepradjustEntry E ON M.FID = E.FID INNER JOIN t_fa_card C ON C.FAlterID = E.FAlterID
  INNER JOIN T_FA_ACCTPOLICY P ON P.FACCTPOLICYID = M.FACCTPOLICYID
   INNER JOIN T_BD_ACCOUNTCALENDAR AC ON P.FACCTCALENDARID = AC.FID
   INNER JOIN T_FA_FINANCE F ON (F.FALTERID = E.FALTERID AND F.FACCTPOLICYID = M.FACCTPOLICYID)
   WHERE ((E.FID IN (100059) AND (E.FDepr > 0)) AND M.FOwnerOrgID = C.FOwnerOrgID)

加粗ID自行替换折旧调整单的FID（不能审核的那条折旧调整单数据的FID）

查看数据发现，FASSETID有重复的找出来，为什么去找FASSETID重复的呢，是看sql语句on后面的条件：

ON
   ((((B.FAssetID = T.FAssetID AND B.FYear = T.FYear) AND B.FPeriod = T.FPeriod) AND B.FAcctPolicyID = T.FAcctPolicyID))

年、月、会计政策没什么好说的，只能看fassetid去匹配看有没有重复的数据

4.通过FASSETID去找到FNUMBER，也就是重复的卡片数据，只有这种重复的卡片数据会导致折旧调整单审核的时候报错如截图上

5.请自行修复数据，或找出根源解决问题！！！

解决方案：

根本原因是由于重复的FASSETID中FISNEWREC = '2'的记录有两条，正常数据是只有一条FISNEWREC = '2'

可以考虑修改数据或删除批量变更单上重复的数据，看实际