Thread: problem with update from subselect

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     

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_timefrom route r where r.routeid=trip.trip_route);

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-0111: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