From: Thrasher <thrasher@fibers.upc.es>
To: pgsql-sql@postgresql.org
Subject: Updating from select
Date: Wed, 02 Oct 2002 19:11:19 +0200
Message-ID: <3D9B28B7.4000903@fibers.upc.es> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: thrasher@fibers.upc.es
Subject: Re: Updating from select
In-Reply-To: <3D9B28B7.4000903@fibers.upc.es>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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