[Newbie] UPDATE based on other table content

[Daniel Chiaramello <daniel.chiaramello@xxxxxxxxx>]

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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general