From: | Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | problem with update from subselect |
Date: | 2002-05-01 17:29:34 |
Message-ID: | E172wxt-0007vR-00@stan.ringways.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | j.konzack | 2002-05-01 19:53:06 | CREATE VIEW question... |
Previous Message | Joe Conway | 2002-05-01 17:09:11 | Re: problem with update from subselect |