Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0I1KuV64663 for ; Thu, 17 Jan 2002 20:20:56 -0500 (EST) (envelope-from sszabo@megazone23.bigpanda.com) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id C89BED5F8; Thu, 17 Jan 2002 17:20:59 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id BE4C85BF4; Thu, 17 Jan 2002 17:20:59 -0800 (PST) Date: Thu, 17 Jan 2002 17:20:59 -0800 (PST) From: Stephan Szabo To: Josh Berkus Cc: Subject: Re: UPDATE Query problem In-Reply-To: Message-ID: <20020117171611.H57470-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200201/212 X-Sequence-Number: 6297 On Thu, 17 Jan 2002, Josh Berkus wrote: > Folks, > > I have a database that contains a chronological journal of activity. For > various reasons, this journal contains both complete and incomplete records, > and while all records are timestamped, the primary key is not strictly ordered > by timestamp. > > What I want to do is update each incomplete record with the contents of the > last previous complete record. As a simple-minded test case: > > CREATE TABLE history AS ( > history_id SERIAL PRIMARY KEY, > period_date TIMESTAMP, > fieldA VARCHAR(30), > fieldB INT4 ); > > CREATE VIEW complete_history_records AS > SELECT history.* > FROM history WHERE fieldA IS NOT NULL > and fieldB IS NOT NULL > > UPDATE history SET fieldA = chr.fieldA > fieldB = chr.fieldB > FROM (SELECT complete_history_records.* > WHERE ??? ) chr > WHERE (history.fieldA IS NULL or > history.fieldB IS NULL); > > The problem is that I cannot figure out a subselect that will allow me to > select the last complete history record prior to the one being updated. It > seems like I need to reference a field in the main query in the subselect, > which can't be done. > > To further hamper things, for portability reasons, I can use neither SELECT > DISTINCT ON nor custom functions. Isn't update...from already an extension? In any case, is performance really important? I think something like: update history set fieldA=chr.fieldA, fieldB=chr.fieldB from complete_history_records chr where (history.fieldA is null or history.fieldB is null) and chr.period_date=(select max(period_date) from complete_history_records where period_date