We help IT Professionals succeed at work.

Update field in 1 table with total of some fields of other table

Hi Experts,

I need to update a field in my table KlantenTransacties with a calculated total from another table.
My SQL does not work. It results in an error 3073.

This is my SQL code:
UPDATE KlantenTransacties
SET KlantenTransacties.TotaalprijsKlant =
(SELECT Sum([Verkoophoeveelheid]*[Verkoopprijs]) AS Totaal
FROM Producten INNER JOIN (KlantenTransacties INNER JOIN KlantenTransactieDetails ON KlantenTransacties.KlantenTransactiesId = KlantenTransactieDetails.KlantentransactieId) ON Producten.ProductId = KlantenTransactieDetails.ProductId
GROUP BY KlantenTransacties.KlantenTransactiesId)

What am I doing wrong?
Comment
Watch Question

try

UPDATE KlantenTransacties as A
INNER JOIN (
      SELECT T2.KlantentransactieId, Sum([Verkoophoeveelheid]*[Verkoopprijs]) AS Totaal
      FROM (Producten as T1
      INNER JOIN KlantenTransactieDetails as T2 ON T1.ProductID = T2.ProductID)
      GROUP BY T2.KlantentransactieId
      ) as B ON A.KlantentransactieId = B.KlantentransactieId
SET a.TotaalprijsKlant = B.Totaal

Commented:
query with aggregate function in select statement are not allowed and are not updatable. You need to modify your query.
As a workaround, try using temporary table.

Commented:
I think that once you have a summary query in there the whole query will be non-updatable. Try putting the subquery in a separate query or temp table first.




 
or create a new query with this (say that we call it groupKantentransactie)

SELECT T2.KlantentransactieId, Sum([Verkoophoeveelheid]*[Verkoopprijs]) AS Totaal
      FROM (Producten as T1
      INNER JOIN KlantenTransactieDetails as T2 ON T1.ProductID = T2.ProductID)
      GROUP BY T2.KlantentransactieId

so then you do

UPDATE KlantenTransacties as A
INNER JOIN groupKantentransactie as B ON A.KlantentransactieId = B.KlantentransactieId
SET a.TotaalprijsKlant = B.Totaal

Author

Commented:
Hi experts,

Thanks for the fast replies. I know a temp table could be the solution. I tried to avoid that with one SQL. I also had already tried the solution ralmada suggested, but this resulted in the same error 3073. So, I'm afraid working with a tempt table will be the only possible solution?
actually once you created the groupKantentransactie query

then the update should be

UPDATE KlantenTransacties as A
SET A.TotaalprijsKlant = DSUM("Totaal", "groupKantentransactie", "KlantentransactieId = " & KlantentransactieId)

or even better with one SQL you can do

UPDATE KlantenTransacties as A
SET A.TotaalprijsKlant = DSUM("[Verkoophoeveelheid]*[Verkoopprijs]", "KlantenTransactieDetails", "KlantentransactieId = " & KlantentransactieId)
so the above, meaning without creating a subquery and just running an update directly.

Now if either [Verkoophoeveelheid] or [Verkoopprijs] are not from KlantenTransactieDetails, but from Producten, then you have no choice but creating the query groupKantentransactie

SELECT T2.KlantentransactieId, Sum([Verkoophoeveelheid]*[Verkoopprijs]) AS Totaal
      FROM (Producten as T1
      INNER JOIN KlantenTransactieDetails as T2 ON T1.ProductID = T2.ProductID)
      GROUP BY T2.KlantentransactieId


and then run the update

UPDATE KlantenTransacties as A
SET A.TotaalprijsKlant = DSUM("Totaal", "groupKantentransactie", "KlantentransactieId = " & KlantentransactieId)


Author

Commented:
Ralmada, you're my hero. Thanks for the fast, complete and very accurate help. In my case I had to choose for the last option because Verkoopprijs is a field in Producten and not in KlantenTransactieDetails.

Thank you, very very much.