使用Join和Group By更新语句 [英] Update Statement using Join and Group By
本文介绍了使用Join和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屋!
查看全文