I have the following SQL query with which I want to update a column,ton in a table UniekeBlokkeHistories. This value is the sum of all the values of column net in table wbridge_history where grower,block,section and oesjaar are the same.
UPDATE UniekeBlokkeHistories
SET ton = (SELECT SUM(t2.net)
FROM wbridge_history t2
WHERE t2.grower = grower
and t2.[block] = [block]
and t2.section = section
and t2.oesjaar = oesjaar )
However, it adds all the values in net and not based on the set of conditions. What am I missing?
;WITH cte AS
(
SELECT
grower,
[block],
[section],
oesjaar,
new_ton = SUM(t2.net)
FROM
wbridge_history t2
GROUP BY
grower,
[block],
[section],
oesjaa
)
UPDATE u
SET [ton] = new_ton
FROM
UniekeBlokkeHistories u
INNER JOIN cte c ON
c.grower = u.grower
AND c.[block] = u.[block]
AND c.section = u.section
AND c.oesjaar = u.oesjaar;
UPDATE UBH
SET ton = wh.total_net
FROM UniekeBlokkeHistories UBH
INNER JOIN (
SELECT grower, block, section, oesjaar, SUM(net) AS total_net
FROM wbridge_history
GROUP BY grower, block, section, oesjaar
) AS wh ON
wh.grower = UBH.grower
and wh.[block] = UBH.[block]
and wh.section = UBH.section
and wh.oesjaar = UBH.oesjaar