Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0I0tgV56906 for ; Thu, 17 Jan 2002 19:55:42 -0500 (EST) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.5.2) with HTTP id 622592 for ; Thu, 17 Jan 2002 16:55:43 -0800 From: "Josh Berkus" Subject: UPDATE Query problem To: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.5.2 Date: Thu, 17 Jan 2002 16:55:43 -0800 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200201/210 X-Sequence-Number: 6295 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. I'm stumped. Please offer suggestions! -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco