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

Re: [Newbie] UPDATE based on other table content

From: Daniel Chiaramello <daniel(dot)chiaramello(at)golog(dot)net>
To: Thom Brown <thombrown(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [Newbie] UPDATE based on other table content
Date: 2009-10-29 16:31:47
Message-ID: 4AE9C373.5000803@golog.net (view raw or flat)
Thread:
Lists: pgsql-general
Thom Brown a écrit :


...
> 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
>>
>>     
> 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

In response to

pgsql-general by date

Next:From: Bob PawleyDate: 2009-10-29 17:08:23
Subject: Can't connect
Previous:From: Sam MasonDate: 2009-10-29 13:41:13
Subject: Re: multiple identical calc and function in single query

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