需要从3中生成一个更新语句 [英] Need to generate one update statement out of the 3

问题描述

UPDATE L
		       SET L.TOT_EXP_INT  = M.INTEREST ,
		           L.TOT_EXP_PRIN = M.REPAY ,
		           L.TOT_ACT_PRIN = LT.AMOUNT  , 
                   L.TOT_EXP_PAYMENT=M.PAYMENT 
           FROM LOANS L
           INNER JOIN MGBALANCE M
           ON M.IDNO=L.IDNO AND M.LOANUMBER=L.LOANUMBER
           INNER JOIN LOAN_TRANS LT
           ON M.IDNO=LT.IDNO AND M.LOANUMBER=LT.ID_CODE         
           WHERE M.PERIOD =@CMONTH AND  M.YEAR1 =@CYEAR 
           AND   LT.PERIOD=@CMONTH AND  LT.YEAR1=@CYEAR AND LT.TRANS_TYPE='PRINCIPAL REPAYMENT'

	
	
		UPDATE L
		       SET L.TOT_EXP_INT  = M.INTEREST ,
		           L.TOT_EXP_PRIN = M.REPAY ,
		           L.TOT_ACT_PAYMENT = LT.AMOUNT  , 
                   L.TOT_EXP_PAYMENT=M.PAYMENT 
           FROM LOANS L
           INNER JOIN MGBALANCE M
           ON M.IDNO=L.IDNO AND M.LOANUMBER=L.LOANUMBER
           INNER JOIN LOAN_TRANS LT
           ON M.IDNO=LT.IDNO AND M.LOANUMBER=LT.ID_CODE         
           WHERE M.PERIOD =@CMONTH AND  M.YEAR1 =@CYEAR 
           AND   LT.PERIOD=@CMONTH AND  LT.YEAR1=@CYEAR AND LT.TRANS_TYPE='TOTAL PAYMENT'

请结合这3个更新程序分段为一个更新语句

Please combine these 3 update program segment into One update statement

推荐答案

我最好的猜测：

My best guess:

UPDATE L
SET L.TOT_EXP_INT  = M.INTEREST ,
L.TOT_EXP_PRIN = M.REPAY ,
L.TOT_ACT_PRIN = LT.AMOUNT  , 
L.TOT_EXP_PAYMENT=M.PAYMENT 
FROM LOANS L
      INNER JOIN MGBALANCE M ON M.IDNO=L.IDNO AND M.LOANUMBER=L.LOANUMBER
      INNER JOIN LOAN_TRANS LT ON M.IDNO=LT.IDNO AND M.LOANUMBER=LT.ID_CODE         
WHERE M.PERIOD =@CMONTH AND  M.YEAR1 =@CYEAR 
    AND LT.PERIOD=@CMONTH AND  LT.YEAR1=@CYEAR
    AND (LT.TRANS_TYPE='PRINCIPAL REPAYMENT' OR LT.TRANS_TYPE='TOTAL PAYMENT')

这篇关于需要从3中生成一个更新语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！