Обсуждение: [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