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
Daniel Chiaramello a écrit :
Hello.
I have a very basic question, relative to the following "problem".
I have the following tables:
product
id
qty
intermediate
id
product_id
orders
intermediate_id
I want to update the "qty" field of the "product" table by
incrementing it each time there is an order in the "orders" table,
referencing a given product through the "intermediate" table.
I tried the following request:
UPDATE
qty = qty+1
FROM
intermediate,
orders
WHERE
orders.intermediate_id=intermediate.id AND
intermediate.product_id=product.id
;
But of course it does what was predictable - ie the qty "field" is
incremented only once, even if more than one entry is referencing a
given product. But it's not what I was hoping...
What would be the "good" solution to do that UPDATE?
Thanks for your attention!
Daniel Chiaramello
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
