pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Updating
2+ messages / 2 participants
[nested] [flat]

* Updating
@ 1999-06-16 17:06  Bob Kruger <bkruger@mindspring.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Bob Kruger @ 1999-06-16 17:06 UTC (permalink / raw)
  To: pgsql-sql


Thanks for all who assisted with my previous question on grouping.

I would like to do an update of a table with the results of a query on
another.

For example:

table vehcost

id		serial
po		varchar(12)
veh_no		varchar(8)
cost		real
comments	varchar(30)


table vehinfo

id            serial
veh_no		varchar(12)
m_cost        real
...


I would like to take the sum of vehcost.veh_no and update that info to
field vehinfo.m_cost.

So far, I have worked with the following without success:

update vehinfo set m_cost = (select sum(vehcost.cost) from vehcost group by
veh_no) where vehinfo.veh_no = vehcost.veh_no ; 

Any ideas.

Thanks in advance for any assistance.

Regards - Bob Kruger





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

* Re: [SQL] Updating
@ 1999-06-16 21:12  Fomichev Michael <fomichev@null.ru>
  0 siblings, 0 replies; 2+ messages in thread

From: Fomichev Michael @ 1999-06-16 21:12 UTC (permalink / raw)
  To: pgsql-sql



On Wed, 16 Jun 1999, Bob Kruger wrote:

> 
> Thanks for all who assisted with my previous question on grouping.
> 
> I would like to do an update of a table with the results of a query on
> another.
> 
> For example:
> 
> table vehcost
> 
> id		serial
> po		varchar(12)
> veh_no		varchar(8)
> cost		real
> comments	varchar(30)
> 
> 
> table vehinfo
> 
> id            serial
> veh_no		varchar(12)
> m_cost        real
> ...
> 
> 
> I would like to take the sum of vehcost.veh_no and update that info to
> field vehinfo.m_cost.
> 
> So far, I have worked with the following without success:
> 
> update vehinfo set m_cost = (select sum(vehcost.cost) from vehcost group by
> veh_no) where vehinfo.veh_no = vehcost.veh_no ; 

This query has an error. You can't use a subquery in `set m_cost = '
expression. So, I think you can't update your table with one query. 
You need something like this:

create view tmp as select veh_no, sum(cost) from vehcost group by veh_no;

Now you have a view like your subquery.

And after that:

update vehinfo set m_cost = tmp.sum from tmp where
vehinfo.veh_no=tmp.veh_no;

Remember `from tmp' clause is PostgreSQL non-standard extension in SQL.  

May be there are another solutions.

Mike.





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


end of thread, other threads:[~1999-06-16 21:12 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-06-16 17:06 Updating Bob Kruger <bkruger@mindspring.com>
1999-06-16 21:12 Re: [SQL] Updating Fomichev Michael <fomichev@null.ru>

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