Thom Brown a écrit :
...
2009/10/29 Daniel Chiaramello <daniel.chiarame...@golog.net>:
Never mind, I found how finally:
UPDATE
product
SET
qty = qty+s_count
FROM (
SELECT
intermediate.product_id,
count(*) AS s_count
FROM
intermediate,
orders
WHERE
orders.intermediate_id=intermediate.id
GROUP BY
intermediate.product_id
) AS summary
WHERE
summary.product_id = product.id
;
Sorry for disturbance!
Daniel
Your solution looks like it would perform a cumulative calculation.
Surely you'd want qty = s_count?
In any case, wouldn't you be better off not having a quantity column
at all and just calculate it with either a query or a view?
Example:
SELECT product.id, COUNT(intermediate.product_id)
FROM product
LEFT JOIN intermediate ON product.id = intermediate.product_id
GROUP BY product.id
ORDER BY product.id
Or include a product name in the product table to get more meaningful
output. You'd then get an output like:
id name count
1 Orange 5
2 Apples 7
3 Pears 2
4 Kiwi 0
If you don't want ones for which there have been no orders for (or
whatever your intermediate table is for), use an INNER JOIN instead.
Regards
Thom
Thanks for anwer, but no, it's really adding the number of entries to my
"qty" field. The "orders" table is a transient one and is cleaned regularly.
Of course, the example I gave is a simplified one (there are no orders
or products, I chose these names to ease the understanding of my
problem) - in reality, the problem is much complicated than that :)
But thanks for answer anyways.
Daniel
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
