From: | Daniel Chiaramello <daniel(dot)chiaramello(at)golog(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: [Newbie] UPDATE based on other table content |
Date: | 2009-10-29 11:57:31 |
Message-ID: | 4AE9832B.4070802@golog.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Thom Brown | 2009-10-29 13:31:42 | Re: [Newbie] UPDATE based on other table content |
Previous Message | A. Kretschmer | 2009-10-29 11:26:10 | Re: Is data compressed when stored? |