From: | Thom Brown <thombrown(at)gmail(dot)com> |
---|---|
To: | Daniel Chiaramello <daniel(dot)chiaramello(at)golog(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: [Newbie] UPDATE based on other table content |
Date: | 2009-10-29 13:31:42 |
Message-ID: | bddc86150910290631g2d407984xc6fbd2d8a6d4edad@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
2009/10/29 Daniel Chiaramello <daniel(dot)chiaramello(at)golog(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Sam Mason | 2009-10-29 13:41:13 | Re: multiple identical calc and function in single query |
Previous Message | Daniel Chiaramello | 2009-10-29 11:57:31 | Re: [Newbie] UPDATE based on other table content |