使用Join和Group By更新语句 [英] Update Statement using Join and Group By

问题描述

我写了下面的Update语句，但是它显示了错误，例如关键字'GROUP'附近的语法错误。

I have written the below Update Statement, but it shows the error such as "Incorrect syntax near the keyword 'GROUP'."

UPDATE 
       J
SET 
       J.StatusID = CASE WHEN SUM(DUV.VendorDUQuantity) = SUM(RD.InvoiceQuantity) THEN 1 ELSE J.StatusID END
FROM
        PLN_DU_Vendor DUV
    INNER JOIN ENG_Release R ON R.ReleaseID = DUV.ReleaseID
    INNER JOIN ENG_DU_Header H ON H.ReleaseID = R.ReleaseID AND DUV.DUID = H.DUID
    INNER JOIN MKT_JobOrder J ON J.JobOrderID = R.JobOrderID
    INNER JOIN MKT_CustomerOrder CO ON CO.OrderID = J.OrderID
    LEFT JOIN PMT_RFDHeader RH ON RH.JobOrderID = J.JobOrderID
    LEFT JOIN PMT_RFDDetail RD ON RD.RFDID = RH.RFDID AND RD.DUID = DUV.DUID                 
WHERE 
        CO.OrderID = 100
GROUP BY 
        J.JobOrderID

取代更新，Select对于上面的查询是完美的。会有什么问题以及如何根据Join和Group By子句编写查询。

Instead of Update, Select is working perfectly for the above Query. What would be the problem and How to write the Query based on Join and Group By clause.

推荐答案

您可以尝试将组放入子查询中，然后通过JobOrderID加入，如下所示：

You can try putting the group by inside of a subquery, then join by the "JobOrderID", like this:

UPDATE J
SET J.StatusID = A.statusId
FROM MKT_JobOrder J
INNER JOIN (
    SELECT J.JobOrderID
        , CASE 
            WHEN SUM(DUV.VendorDUQuantity) = SUM(RD.InvoiceQuantity)
                THEN 1
            ELSE J.StatusID
            END AS statusId 
    FROM PLN_DU_Vendor DUV
    INNER JOIN ENG_Release R ON R.ReleaseID = DUV.ReleaseID
    INNER JOIN ENG_DU_Header H ON H.ReleaseID = R.ReleaseID
                               AND DUV.DUID = H.DUID
    INNER JOIN MKT_JobOrder J ON J.JobOrderID = R.JobOrderID
    INNER JOIN MKT_CustomerOrder CO ON CO.OrderID = J.OrderID
    LEFT JOIN PMT_RFDHeader RH ON RH.JobOrderID = J.JobOrderID
    LEFT JOIN PMT_RFDDetail RD ON RD.RFDID = RH.RFDID
                               AND RD.DUID = DUV.DUID
    WHERE CO.OrderID = 100
    GROUP BY J.JobOrderID
           , J.StatusID
    ) A ON J.JobOrderID = A.JobOrderID

这篇关于使用Join和Group By更新语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！