SQL更新为其连接值的总和

Question

我正在尝试将数据库中的一个字段更新为其连接值的总和：

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."

有任何想法吗？

- Mark Clancy

---

你想做什么？你是想用所有相应的dbo.BookingPitcheExtras记录的“price”列的总和来更新一些dbo.BookingPitches记录吗？ - Patrick Karcher

如果你要使用 SUM，你需要指定你想要分组的字段。 - FrustratedWithFormsDesigner

这个语句没有任何意义。你到底想做什么？ - Ian Henry

Answer 1

这个怎么样：

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

- JonH

---

今天我使用这个语法作为更新语句的指南，效果非常好。顺便提一下，确保精准使用此处别名的值，开始时我没有这样做，花了一点时间才解决了问题。 - Dylan Hayes

Answer 2

除了上述解决方案，还可以使用表别名：

UPDATE T1 SET T1.extrasPrice = (SELECT SUM(T2.Price) FROM BookingPitchExtras T2 WHERE T2.pitchID = T1.ID)
FROM BookingPitches T1;

- Muhammad Omar ElShourbagy

---

我必须更改变量，例如UPDATE BookingPitches SET extrasPrice =。 - suhailvs

Answer 3

我遇到了相同的问题，并发现可以使用公共表达式（在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

- C-Pound Guru

Answer 4

这是一个有效的错误。请查看这里。以下（以及下面建议的其他方法）是实现此目标的方法：

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

- Ashish Gupta

---

@Ashish Gupta - 看起来很像我的查询...嗯 - JonH

抱歉...需要我删除回答吗？当我在撰写查询并阅读那篇文章时，我没有加载答案。 - Ashish Gupta

不，没关系，我只是觉得奇怪我们甚至使用了相同的临时存储t，并且查询几乎完全相同。两个答案比一个更好。 - JonH

JonH，我会在写答案的同时学习如何加载答案。再次抱歉，我不是有意的。我也编辑了我的答案。 - Ashish Gupta

Answer 5

你需要类似这样的代码：

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

- MartW

Answer 6

使用PostgreSQL，我需要调整这个解决方案才能让它适合我的情况：

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

- Yoric

Answer 7

使用类似以下的子查询。

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

- cmsjr