Message-ID: <3D9B28B7.4000903@fibers.upc.es>
Date: Wed, 02 Oct 2002 19:11:19 +0200
From: Thrasher <thrasher@fibers.upc.es>
Organization: =?ISO-8859-15?Q?Delegaci=F3_Alumnes_FIB?=
User-Agent: Mozilla/5.0 (Windows; U; Win98; en-US; rv:1.1b) Gecko/20020721
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Updating from select
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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