Update Entry with SUM from other table

[Clumsy]

Hello community,



I am trying to update the entry with the ID "134" of the table "tbl_offers" with the sum of its Products (found in table "tbl_products").
That's what i got so far, but it does not work...

UPDATE tbl_offers SET tbl_offers.total = (SELECT SUM(tbl_products.price) FROM tbl_products WHERE tbl_products.IDoffer = 134) WHERE tbl_offers.ID = 134;

tbl_offers:

ID	total
134	??? (should be 15)
...

tbl_products:

ID	IDoffer	price
1	134	10
2	134	5
3	999	80
...

Would be thankful for any suggestions/help.
Greetings from Bavaria, Germany
Flo

[Welshgasman]

Basically you do not, you just calculate when needed.

[Clumsy]

Hey, could you please explain in a bit more details?

You suggest, that i do not save this information at all? -> Than my next Question would be, how to get all offers (sorted by total)?
And even though it might not be necessary, I'd still like to know what i did wrong, so I can improve myself.

Thanks for your reply!

[Welshgasman]

Generally it is not recommended to store anything that you can calculate, as you are doing now.
One might store a tax rate for a particularly period, rather than looking it up, like on order details, but in your case here, you would just use that inner query.
As soon as you add a new record to tblOffers, that is out of date.? Bit like storing someone's age, going to be out of date within the year?, store the DOB instead, and calculate when needed.

For your particular request, you would sum and group by IDOffer

TBH, that SQL looks fine to me, but without data, I cannot test?
I would break it down into small steps. Does that inner SQL work?, it should, then hardcode that number into the Update, does that work?, then put it all together.

Edit: Always my first point of call
https://www.google.com/search?q=upda...client=gws-wiz

[CarlettoFed]

You have to replace

UPDATE tbl_offers SET tbl_offers.total = (SELECT SUM(tbl_products.price) FROM tbl_products WHERE tbl_products.IDoffer = 134) WHERE tbl_offers.ID = 134;

with

UPDATE tbl_offers SET tbl_offers.total = DSum("price","tbl_products","IDoffer = 134") WHERE tbl_offers.ID=134

[Minty]

As Gasman has suggested simply use a query to get your results

SELECT IDOffer , Sum(price) as TotalOffers
FROM tbl_products
GROUP BY IDOffer

It will always be accurate and doesn't need you to monitor for changes to the underlying table.