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
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