sumar - sql varianza

Actualización de SQL a la SUMA de sus valores unidos.

Question

Estoy intentando actualizar un campo en la base de datos a la suma de sus valores unidos:

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

Cuando ejecuto esto me sale el siguiente error:

"An aggregate may not appear in the set list of an UPDATE statement."

¿Algunas ideas?

Answer 1

Con Postgres, tuve que ajustar la solución para que esto funcionara para mí:

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

Answer 2

Este es un error válido. Ver this A continuación (y otros sugeridos a continuación) están las formas de lograr esto:

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

Answer 3

Me encontré con el mismo problema y descubrí que podía resolverlo con una expresión de tabla común (disponible en SQL 2005 o posterior):

;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

Answer 4

Necesitas algo como esto:

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

Answer 5

Qué tal esto:

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

Answer 6

Una alternativa a las soluciones anteriores es usar Alias ​​para Tablas:

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

Answer 7

Utilice una sub consulta similar a la siguiente.

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