Обсуждение: [Newbie] UPDATE based on other table content
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
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 >
2009/10/29 Daniel Chiaramello <daniel.chiaramello@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 > > 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
Thom Brown a écrit : ... > 2009/10/29 Daniel Chiaramello <daniel.chiaramello@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