mysql更新语句在同一个表上使用from子句

Question

以下是MS Sql server Update语句

    Update 
        HC_TranDetails
SET
        InsPayments = (SELECT IsNull(SUM(ISNULL(CreditAmount,0)),0) From HC_TranDetails TDS
                        Where TDS.TransactionType = 2 
                        AND TDS.ClaimNo = TD.ClaimNo 
                        AND TDS.LineItemNo = TD.LineItemNo 
                        AND IsNull(TDS.InsPlanRowID,'') <> ''
                        AND TDS.ReverseEntry <> 1 ),

        Adjustments = (SELECT IsNull(SUM(ISNULL(CreditAmount,0)),0) From HC_TranDetails TDS
                        Where TDS.TransactionType = 8
                        AND TDS.ClaimNo = TD.ClaimNo 
                        AND TDS.LineItemNo = TD.LineItemNo 
                        AND IsNull(TDS.InsPlanRowID,'') <> ''
                        AND TDS.ReverseEntry <> 1 ),


FROM
        HC_TranDetails TD

现在我正在 mysql 中尝试相同类型的语句，如下

    UPDATE claimdetails SET balanceAmount =  (SELECT IFNULL(SUM(IFNULL(debitamount,0)) -  SUM(IFNULL(creditamount,0)),0) 
                      FROM claimdetail CD WHERE CD.claimID = CDS.claimID)

FROM ClaimDetail CDS

但它在第 4 行的“From ClaimDetail CDS”附近显示为语法错误

Answer 1

MySQL 对于同一个表上的更新很挑剔。简单的解决方法是包含额外的子查询级别。不过，正确的解决方法是使用联接

UPDATE claimdetails join
       (select claimid, IFNULL(SUM(IFNULL(debitamount,0)) -  SUM(IFNULL(creditamount,0)),0) as val
        from ClaimDetails
        group by claimid
       ) agg
       on claimdetails.claimid = agg.claimid
    SET balanceAmount = agg.val;