Skip site navigation (1) Skip section navigation (2)

Re: [Newbie] UPDATE based on other table content

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 (view raw or flat)
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

In response to

Responses

pgsql-general by date

Next:From: Sam MasonDate: 2009-10-29 13:41:13
Subject: Re: multiple identical calc and function in single query
Previous:From: Daniel ChiaramelloDate: 2009-10-29 11:57:31
Subject: Re: [Newbie] UPDATE based on other table content

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group