Re: [Newbie] UPDATE based on other table content - Mailing list 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
>