更新sum（字段）给出错误 - 我如何克服它 [英] Update with sum(fields) giving error - how do I overcome it

问题描述

 更新 L 
  SET  L.TOT_EXP_INT = L.TOT_EXP_INT + SUM（M.INTEREST），
 L.TOT_EXP_PRIN = L.TOT_EXP_PRIN + SUM（M.REPAY），
 L.TOT_EXP_PAYMENT = L.TOT_EXP_PRIN + SUM（M.PAYMENT）
  FROM  LOANS L 
  INNER   JOIN  MGBALANCE M 
  ON  M.IDNO = L.IDNO  AND  M.LOANUMBER = L.LOANUMBER 
 
 
 更新 ML 
  SET  ML.TOT_ACT_INT = ML.TOT_ACT_INT + LT.AMOUNT 
  FROM 贷款ML 
  INNER   JOIN  LOAN_TRANS LT 
  ON  ML.IDNO = LT.IDNO  AND  ML.LOANUMBER = LT.ID_CO DE 
  WHERE  LT.TRANS_TYPE = '  INTEREST还款' 

我的尝试：



在SQL SERVER 2008中尝试了我自己的代码

解决方案

你的问题并不完全清楚，但我会试一试。我想你只有第一个语句有问题而且你得到聚合可能不会出现在UPDATE语句的集合列表中。从错误消息中可以清楚地看出，您不能将聚合与更新一起使用。但您可以预先将数据分组到CTE中，然后加入更新中的数据：

   MGBALANCE_GROUPED  as  
（
  SELECT  IDNO，LOANUMBER，
 SUM（ INTEREST） as  INTEREST，
 SUM（REPAY） as  REPAY，
 SUM（付款） as 付款
  FROM  MGBALANCE 
  GROUP   BY  IDNO，LOANUMBER 
）
 更新 L 
  SET  L.TOT_EXP_INT = L.TOT_EXP_INT + M.INTEREST，
 L.TOT_EXP_PRIN = L.TOT_EXP_PRIN + M.REPAY，
 L.TOT_EXP_PAYMENT = L.TOT_EXP_PRIN + M.PAYMENT 
  FROM  LOANS L 
  INNER   JOIN  MGBALANCE_GROUPED M 
  ON  M.IDNO = L.IDNO  AND  M.LOANUMBER = L.LOANUMBER 

查看 SQL小提琴 [ ^ ]。

UPDATE L
   SET L.TOT_EXP_INT    = L.TOT_EXP_INT  + SUM(M.INTEREST) ,
       L.TOT_EXP_PRIN   = L.TOT_EXP_PRIN + SUM(M.REPAY),
       L.TOT_EXP_PAYMENT= L.TOT_EXP_PRIN + SUM(M.PAYMENT) 
FROM LOANS L
INNER JOIN MGBALANCE M
ON M.IDNO=L.IDNO AND M.LOANUMBER=L.LOANUMBER
    	
       
UPDATE ML
       SET  ML.TOT_ACT_INT =  ML.TOT_ACT_INT + LT.AMOUNT
   FROM LOANS ML
   INNER JOIN LOAN_TRANS LT
   ON ML.IDNO=LT.IDNO AND ML.LOANUMBER=LT.ID_CODE         
  WHERE  LT.TRANS_TYPE= 'INTEREST REPAYMENT'

What I have tried:

TRIED MY OWN CODES IN SQL SERVER 2008

解决方案

Your question is not completely clear but I will give it a try. I suppose you only have problem with the first statement and you are getting "An aggregate may not appear in the set list of an UPDATE statement". From the error message it should be clear that you cannot use aggregates with an update. But you can group the data in a CTE upfront and then join to that in your update:

with MGBALANCE_GROUPED as
(
  SELECT IDNO, LOANUMBER, 
    SUM(INTEREST) as INTEREST,
    SUM(REPAY) as REPAY,
    SUM(PAYMENT) as PAYMENT
  FROM MGBALANCE
  GROUP BY IDNO, LOANUMBER
)
UPDATE L
   SET L.TOT_EXP_INT    = L.TOT_EXP_INT  + M.INTEREST,
       L.TOT_EXP_PRIN   = L.TOT_EXP_PRIN + M.REPAY,
       L.TOT_EXP_PAYMENT= L.TOT_EXP_PRIN + M.PAYMENT 
FROM LOANS L
INNER JOIN MGBALANCE_GROUPED M
ON M.IDNO=L.IDNO AND M.LOANUMBER=L.LOANUMBER

Check out the SQL Fiddle[^].

这篇关于更新sum（字段）给出错误 - 我如何克服它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！