Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 5DE884761A3 for ; Wed, 1 May 2002 13:04:30 -0400 (EDT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (Postfix) with ESMTP id 786EF475D4B for ; Wed, 1 May 2002 12:55:52 -0400 (EDT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id DAAF0D608; Wed, 1 May 2002 09:55:47 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id D05605C01; Wed, 1 May 2002 09:55:47 -0700 (PDT) Date: Wed, 1 May 2002 09:55:47 -0700 (PDT) From: Stephan Szabo To: Gary Stainburn Cc: Subject: Re: problem with update from subselect In-Reply-To: Message-ID: <20020501095436.Q24344-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200205/4 X-Sequence-Number: 7640 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);