Thread: Updating from select
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
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) ServusManfred
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 > >