pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Updating from select
3+ messages / 2 participants
[nested] [flat]

* Updating from select
@ 2002-10-02 17:11  Thrasher <thrasher@fibers.upc.es>
  0 siblings, 1 reply; 3+ messages in thread

From: Thrasher @ 2002-10-02 17:11 UTC (permalink / raw)
  To: pgsql-sql

Hi all

I have two related tables:

CREATE TABLE trans_log (
	id	serial,
	date	timestamp not null,
	cost	numeric(12,5) NULL
);

CREATE TABLE products_log (
	id	serial,
	trans	integer not null references trans_log(id),
	cost	numeric(12,5) NOT NULL
);

So, a transaction can have from zero to a lot of products (1:N)

Ok. I have data in the tables. The transaction tables have their id and 
date, but no costs, so costs = 0.00000 for all transactions.

The products do have their cost set.

I can do a

SELECT trans, SUM(cost) FROM products_log GROUP BY trans;

to get the total amount of each transactions.

What I would like is to update the transactions table with the sum of 
its products cost, some kind of

UPDATE trans_log t SET t.cost =
   (SELECT SUM(p.cost) FROM products_log p WHERE p.trans = t.id)

But I'm not able.

Thanks in advance

Thrasher




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Updating from select
@ 2002-10-02 17:45  Manfred Koizar <mkoi-pg@aon.at>
  parent: Thrasher <thrasher@fibers.upc.es>
  0 siblings, 1 reply; 3+ messages in thread

From: Manfred Koizar @ 2002-10-02 17:45 UTC (permalink / raw)
  To: Thrasher <thrasher@fibers.upc.es>; +Cc: pgsql-sql

On Wed, 02 Oct 2002 19:11:19 +0200, Thrasher <thrasher@fibers.upc.es>
wrote:
>UPDATE trans_log t SET t.cost =
>   (SELECT SUM(p.cost) FROM products_log p WHERE p.trans = t.id)

Thrasher, try it without the table alias t:

UPDATE trans_log SET cost =
   (SELECT SUM(p.cost) FROM products_log p
     WHERE p.trans = trans_log.id)

Servus
 Manfred



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Updating from select
@ 2002-10-03 08:18  Thrasher <thrasher@fibers.upc.es>
  parent: Manfred Koizar <mkoi-pg@aon.at>
  0 siblings, 0 replies; 3+ messages in thread

From: Thrasher @ 2002-10-03 08:18 UTC (permalink / raw)
  To: pgsql-sql

Hi Manfred,

Such a silly thing blocked me, and I was about to write a stored procedure.

Thanks a lot !!

BTW, does anyone knows about a RH7.2 or newer RPM of v7.3? Do I need any 
other tools not included in RH7.2 to compile it?

Thrasher

Manfred Koizar wrote:
> On Wed, 02 Oct 2002 19:11:19 +0200, Thrasher <thrasher@fibers.upc.es>
> wrote:
> 
>>UPDATE trans_log t SET t.cost =
>>  (SELECT SUM(p.cost) FROM products_log p WHERE p.trans = t.id)
> 
> 
> Thrasher, try it without the table alias t:
> 
> UPDATE trans_log SET cost =
>    (SELECT SUM(p.cost) FROM products_log p
>      WHERE p.trans = trans_log.id)
> 
> Servus
>  Manfred
> 
> 




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2002-10-03 08:18 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-02 17:11 Updating from select Thrasher <thrasher@fibers.upc.es>
2002-10-02 17:45 ` Manfred Koizar <mkoi-pg@aon.at>
2002-10-03 08:18   ` Thrasher <thrasher@fibers.upc.es>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox