pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: pgsql-sql@postgresql.org
Subject: UPDATE Query problem
Date: Thu, 17 Jan 2002 16:55:43 -0800
Message-ID: <web-622592@davinci.ethosmedia.com> (raw)

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



view thread (7+ messages)  latest in thread

Message-ID: <web-622592@davinci.ethosmedia.com>
Permalink:  ../web-622592@davinci.ethosmedia.com/
Also on:    postgresql.org/message-id/web-622592@davinci.ethosmedia.com

 · 

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: josh@agliodbs.com
  Subject: Re: UPDATE Query problem
  In-Reply-To: <web-622592@davinci.ethosmedia.com>

* 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