SQL 更新到其联接值的 SUM [英] SQL Update to the SUM of its joined values
本文介绍了SQL 更新到其联接值的 SUM的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试将数据库中的字段更新为其连接值的总和:
I'm trying to update a field in the database to the sum of its joined values:
UPDATE P
SET extrasPrice = SUM(E.price)
FROM dbo.BookingPitchExtras AS E
INNER JOIN dbo.BookingPitches AS P ON E.pitchID = P.ID
AND P.bookingID = 1
WHERE E.[required] = 1
运行时出现以下错误:
"An aggregate may not appear in the set list of an UPDATE statement."
有什么想法吗?
推荐答案
这个怎么样:
UPDATE p
SET p.extrasPrice = t.sumPrice
FROM BookingPitches AS p
INNER JOIN
(
SELECT PitchID, SUM(Price) sumPrice
FROM BookingPitchExtras
WHERE [required] = 1
GROUP BY PitchID
) t
ON t.PitchID = p.ID
WHERE p.bookingID = 1
这篇关于SQL 更新到其联接值的 SUM的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文