pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: problem with update from subselect
3+ messages / 3 participants
[nested] [flat]
* Re: problem with update from subselect
@ 2002-05-01 16:55 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
1 sibling, 0 replies; 3+ messages in thread
From: Stephan Szabo @ 2002-05-01 16:55 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
On Wed, 1 May 2002, Gary Stainburn wrote:
> Hi all, using the schema described below, I want to be able to update each
> arrival time from departure times and trip lengths.
>
> However the update fails because the subselect returns all three answers.
>
> How would I correct the update to make it work
>
> update trip set trip_arrive = (select t.trip_depart + r.route_time
> from route r, trip t where r.routeid = t.trip_route);
Do you really want to join with another "trip" in the subselect?
I think you probably want something like:
update trip set trip_arrive = (select trip.trip_depart + r.route_time
from route r where r.routeid=trip.trip_route);
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: problem with update from subselect
@ 2002-05-01 17:09 Joe Conway <mail@joeconway.com>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
1 sibling, 0 replies; 3+ messages in thread
From: Joe Conway @ 2002-05-01 17:09 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
Gary Stainburn wrote:
> Hi all, using the schema described below, I want to be able to update each
> arrival time from departure times and trip lengths.
>
> However the update fails because the subselect returns all three answers.
>
> How would I correct the update to make it work
>
> update trip set trip_arrive = (select t.trip_depart + r.route_time
> from route r, trip t where r.routeid = t.trip_route);
>
If I understand what you're trying to do correctly, this works:
test=# update trip set trip_arrive = trip_depart + r.route_time from
route r where r.routeid = trip.trip_route; UPDATE 3
test=# select * from trip;
tripid | trip_route | trip_depart | trip_arrive
--------+------------+---------------------+---------------------
1 | 1 | 2002-01-01 10:00:00 | 2002-01-01 11:40:00
2 | 1 | 2002-02-01 11:30:00 | 2002-02-01 13:10:00
3 | 2 | 2002-01-01 11:00:00 | 2002-01-01 11:30:00
(3 rows)
HTH,
Joe
^ permalink raw reply [nested|flat] 3+ messages in thread
* problem with update from subselect
@ 2002-05-01 17:29 Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 2 replies; 3+ messages in thread
From: Gary Stainburn @ 2002-05-01 17:29 UTC (permalink / raw)
To: pgsql-sql
Hi all, using the schema described below, I want to be able to update each
arrival time from departure times and trip lengths.
However the update fails because the subselect returns all three answers.
How would I correct the update to make it work
update trip set trip_arrive = (select t.trip_depart + r.route_time
from route r, trip t where r.routeid = t.trip_route);
Database definition:
drop table route;
drop table trip;
drop sequence route_id_seq;
drop sequence trip_id_seq;
create sequence "route_id_seq" start 1 increment 1;
create sequence "trip_id_seq" start 1 increment 1;
create table route (
routeid int4 unique default nextval('route_id_seq'::text) not null,
route_depart character (4), -- std ICAO code e.g. EGNM
route_dest character (4), -- ditto
route_time interval,
primary key (routeid)
);
create table trip (
tripid int4 unique default nextval('trip_id_seq'::text) not null,
trip_route int4 references route(routeid),
trip_depart timestamp, -- departure time
trip_arrive timestamp, -- calculated ETA
primary key (tripid)
);
insert into route values (1, 'EGNM', 'EGLL', '1 hour 40 minutes');
insert into route values (2, 'EGLL', 'EGKK', '30 minutes');
insert into trip values (1, 1, '2002-01-01 10:00:00');
insert into trip values (2, 1, '2002-02-01 11:30:00');
insert into trip values (3, 2, '2002-01-01 11:00:00');
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-05-01 17:29 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-05-01 17:29 problem with update from subselect Gary Stainburn <gary.stainburn@ringways.co.uk>
2002-05-01 16:55 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-05-01 17:09 ` Joe Conway <mail@joeconway.com>
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