2009/10/29 Daniel Chiaramello <daniel.chiaramello@xxxxxxxxx>: > 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 >> > 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
