pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUpdating
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