Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 53587475BC4 for ; Wed, 1 May 2002 12:31:33 -0400 (EDT) Received: from stan.ringways.co.uk (unknown [213.38.87.130]) by postgresql.org (Postfix) with ESMTP id 38D93475B69 for ; Wed, 1 May 2002 12:31:31 -0400 (EDT) Received: from gary.ringways.co.uk ([10.1.1.2] helo=there) by stan.ringways.co.uk with smtp (Exim 3.33 #2) id 172wxt-0007vR-00 for pgsql-sql@postgresql.org; Wed, 01 May 2002 17:28:25 +0100 Content-Type: text/plain; charset="iso-8859-1" From: Gary Stainburn Organization: Ringways Garages Ltd To: pgsql-sql@postgresql.org Subject: problem with update from subselect Date: Wed, 1 May 2002 18:29:34 +0100 X-Mailer: KMail [version 1.3.2] MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-Id: X-Archive-Number: 200205/1 X-Sequence-Number: 7637 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