pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@postgresql.org
Subject: problem with update from subselect
Date: Wed, 1 May 2002 18:29:34 +0100
Message-ID: <E172wxt-0007vR-00@stan.ringways.co.uk> (raw)

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     



view thread (3+ messages)

Message-ID: <E172wxt-0007vR-00@stan.ringways.co.uk>
Permalink:  ../E172wxt-0007vR-00@stan.ringways.co.uk/
Also on:    postgresql.org/message-id/E172wxt-0007vR-00@stan.ringways.co.uk

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: gary.stainburn@ringways.co.uk
  Subject: Re: problem with update from subselect
  In-Reply-To: <E172wxt-0007vR-00@stan.ringways.co.uk>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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