Update Statement using Join and Group By

Question

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 

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.

Answer 1

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