Update Statement using Join and Group By
80,052
Solution 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
Solution 2
User sub query instead to perform this operation
UPDATE
J
SET StatusID = x.Status
FROM
(select CASE WHEN SUM(DUV.VendorDUQuantity) = SUM(RD.InvoiceQuantity) THEN 1
ELSE J.StatusID END as Status
JobOrderID
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) X
INNER JOIN MKT_JobOrder J On x.JobOrderID = J.JobOrderID
Author by
thevan
Software Engineering Senior Analyst at Accenture Solutions Private Limited, Chennai, India. Interested in ASP.Net, MVC, Web API, WCF, Web Services, ADO.Net, C#.Net, VB.Net, Entity Framework, MS SQLServer, Angular.js, JavaScript, JQuery, Ajax, HTML and CSS
Updated on October 01, 2020Comments
-
thevan over 3 years
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.