UPDATE Query problem

Josh Berkus <josh@agliodbs.com>

From: "Josh Berkus" <josh@agliodbs.com>
To: pgsql-sql@postgresql.org
Date: 2002-01-18T00:55:43Z
Lists: pgsql-hackers
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