SQL 更新到其联接值的 SUM
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2502032/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Update to the SUM of its joined values
提问by markvpc
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
When I run this I get the following error:
当我运行它时，我收到以下错误：
"An aggregate may not appear in the set list of an UPDATE statement."
Any ideas?
有任何想法吗？
回答by JonH
How about this:
这个怎么样：
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
回答by Muhammad Omar ElShourbagy
An alternate to the above solutions is using Aliases for Tables:
上述解决方案的替代方案是使用表别名：
UPDATE T1 SET T1.extrasPrice = (SELECT SUM(T2.Price) FROM BookingPitchExtras T2 WHERE T2.pitchID = T1.ID)
FROM BookingPitches T1;
回答by Ashish Gupta
This is a valid error. See this. Following (and others suggested below) are the ways to achieve this:-
这是一个有效的错误。看到这个。以下（以及下面建议的其他人）是实现这一目标的方法：-
UPDATE P
SET extrasPrice = t.TotalPrice
FROM BookingPitches AS P INNER JOIN
(
SELECT
PitchID,
SUM(Price) TotalPrice
FROM
BookingPitchExtras
GROUP BY PitchID
) t
ON t.PitchID = p.ID
回答by C-Pound Guru
I ran into the same issue and found that I could solve it with a Common Table Expression(available in SQL 2005 or later):
我遇到了同样的问题，发现我可以使用通用表表达式（在 SQL 2005 或更高版本中可用）来解决它：
;with cte as (
SELECT PitchID, SUM(Price) somePrice
FROM BookingPitchExtras
WHERE [required] = 1
GROUP BY PitchID)
UPDATE p SET p.extrasPrice=cte.SomePrice
FROM BookingPitches p INNER JOIN cte ON p.ID=cte.PitchID
WHERE p.BookingID=1
回答by Yoric
With postgres, I had to adjust the solution with this to work for me:
使用 postgres，我不得不调整解决方案来对我来说有效：
UPDATE BookingPitches AS p
SET extrasPrice = t.sumPrice
FROM
(
SELECT PitchID, SUM(Price) sumPrice
FROM BookingPitchExtras
WHERE [required] = 1
GROUP BY PitchID
) t
WHERE t.PitchID = p.ID AND p.bookingID = 1
回答by MartW
You need something like this :
你需要这样的东西：
UPDATE P
SET ExtrasPrice = E.TotalPrice
FROM dbo.BookingPitches AS P
INNER JOIN (SELECT BPE.PitchID, Sum(BPE.Price) AS TotalPrice
FROM BookingPitchExtras AS BPE
WHERE BPE.[Required] = 1
GROUP BY BPE.PitchID) AS E ON P.ID = E.PitchID
WHERE P.BookingID = 1
回答by cmsjr
Use a sub query similar to the below.
使用类似于下面的子查询。
UPDATE P
SET extrasPrice = sub.TotalPrice from
BookingPitches p
inner join
(Select PitchID, Sum(Price) TotalPrice
from dbo.BookingPitchExtras
Where [Required] = 1
Group by Pitchid
) as Sub
on p.Id = e.PitchId
where p.BookingId = 1
