pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
UPDATE Query problem
7+ messages / 3 participants
[nested] [flat]

* UPDATE Query problem
@ 2002-01-18 00:55 Josh Berkus <josh@agliodbs.com>
  2002-01-18 01:20 ` Re: UPDATE Query problem Tom Lane <tgl@sss.pgh.pa.us>
  2002-01-18 01:20 ` Re: UPDATE Query problem Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 2 replies; 7+ messages in thread

From: Josh Berkus @ 2002-01-18 00:55 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: UPDATE Query problem
  2002-01-18 00:55 UPDATE Query problem Josh Berkus <josh@agliodbs.com>
@ 2002-01-18 01:20 ` Tom Lane <tgl@sss.pgh.pa.us>
  2002-01-18 03:59   ` Re: UPDATE Query problem Josh Berkus <josh@agliodbs.com>
  1 sibling, 1 reply; 7+ messages in thread

From: Tom Lane @ 2002-01-18 01:20 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

"Josh Berkus" <josh@agliodbs.com> writes:
> 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.

Sure you can.  You can't alias history in the UPDATE, but you can alias
it in the subselect, so:

UPDATE history SET fieldA =
  (SELECT fieldA FROM history older
   WHERE older.key = history.key AND
   older.fieldA IS NOT NULL AND older.fieldB IS NOT NULL AND
   older.timestamp =
     (SELECT max(timestamp) FROM history oldest
      WHERE oldest.key = history.key AND
      oldest.fieldA IS NOT NULL AND oldest.fieldB IS NOT NULL)),
fieldB = (SELECT fieldB FROM ... repeat entire subselect above ...)
WHERE (history.fieldA IS NULL or 
       history.fieldB IS NULL);

This will work and (AFAIK) is fully SQL-compliant, but it will be
slower than the dickens because of all those subselects :-(.  Might
be tolerable if the key field is near-unique and is indexed, but
heaven help you if not.

> To further hamper things, for portability reasons, I can use neither SELECT
>  DISTINCT ON nor custom functions.  

Too bad.  SELECT DISTINCT ON would let you get rid of the bottom SELECT
max() and would let you exploit an index on (key,timestamp).  By the
time the query above finishes running, very likely you could talk your
boss into accepting a nonstandard solution ;-)

Also, just because PG can handle the above doesn't mean every RDBMS does
(do I need to name names?).  What products do you really need it to
be portable to?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: UPDATE Query problem
  2002-01-18 00:55 UPDATE Query problem Josh Berkus <josh@agliodbs.com>
  2002-01-18 01:20 ` Re: UPDATE Query problem Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-01-18 03:59   ` Josh Berkus <josh@agliodbs.com>
  2002-01-18 04:14     ` Re: UPDATE Query problem Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: Josh Berkus @ 2002-01-18 03:59 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

Tom,

> Sure you can.  You can't alias history in the UPDATE, but you can alias
> it in the subselect, so:
> 
> UPDATE history SET fieldA =
>   (SELECT fieldA FROM history older
>    WHERE older.key = history.key AND
>    older.fieldA IS NOT NULL AND older.fieldB IS NOT NULL AND
>    older.timestamp =
>      (SELECT max(timestamp) FROM history oldest
>       WHERE oldest.key = history.key AND
>       oldest.fieldA IS NOT NULL AND oldest.fieldB IS NOT NULL)),
> fieldB = (SELECT fieldB FROM ... repeat entire subselect above ...)
> WHERE (history.fieldA IS NULL or 
>        history.fieldB IS NULL);

Interesting.  however, it appears to give me the most recent record with
 non-NULL values.  What I want is the most recent record with non-NULL values
 *before* the record I'm trying to update.  In other words, if I have the
 following data:

history
id	timestamp	fieldA	fieldB
1341	6/30/00		KCKG	1
1345	7/31/00		KC	1
1402	8/31/00		NULL	NULL
2799	9/30/00		NULL	NULL
1581	10/31/00	KC	2
1673	11/30/00	KC	2

I want records 1402 and 2799 to be updated from record 1345, not from record
 1673.

> This will work and (AFAIK) is fully SQL-compliant, but it will be
> slower than the dickens because of all those subselects :-(.  Might
> be tolerable if the key field is near-unique and is indexed, but
> heaven help you if not.

The key field is unique.  And slow is OK ... the history-correction program
 runs overnight.  I just can't afford to take a procedural approach and correct
 one record at a time ... there are 200,000 records and growing at a rate of
 8,000 records per month.

> Also, just because PG can handle the above doesn't mean every RDBMS does
> (do I need to name names?).  What products do you really need it to
> be portable to?

Yeah, you guessed it ... MS SQL Server 7.  Which kills custom functions or
 custom aggregates, something that would make this whole process a lot easier.

Thanks for the help!

-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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: UPDATE Query problem
  2002-01-18 00:55 UPDATE Query problem Josh Berkus <josh@agliodbs.com>
  2002-01-18 01:20 ` Re: UPDATE Query problem Tom Lane <tgl@sss.pgh.pa.us>
  2002-01-18 03:59   ` Re: UPDATE Query problem Josh Berkus <josh@agliodbs.com>
@ 2002-01-18 04:14     ` Tom Lane <tgl@sss.pgh.pa.us>
  2002-01-18 05:18       ` Re: UPDATE Query problem Josh Berkus <josh@agliodbs.com>
  2002-01-18 23:02       ` Re: UPDATE Query problem Josh Berkus <josh@agliodbs.com>
  0 siblings, 2 replies; 7+ messages in thread

From: Tom Lane @ 2002-01-18 04:14 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

"Josh Berkus" <josh@agliodbs.com> writes:
> Interesting.  however, it appears to give me the most recent record with
>  non-NULL values.  What I want is the most recent record with non-NULL values
>  *before* the record I'm trying to update.

Oh, I'm sorry: forgot the extra qualification on the innermost SELECT:

	AND oldest.timestamp < history.timestamp

> Yeah, you guessed it ... MS SQL Server 7.

I dunno, how good is SQL Server on subselects?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: UPDATE Query problem
  2002-01-18 00:55 UPDATE Query problem Josh Berkus <josh@agliodbs.com>
  2002-01-18 01:20 ` Re: UPDATE Query problem Tom Lane <tgl@sss.pgh.pa.us>
  2002-01-18 03:59   ` Re: UPDATE Query problem Josh Berkus <josh@agliodbs.com>
  2002-01-18 04:14     ` Re: UPDATE Query problem Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-01-18 05:18       ` Josh Berkus <josh@agliodbs.com>
  1 sibling, 0 replies; 7+ messages in thread

From: Josh Berkus @ 2002-01-18 05:18 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

Tom,

> Oh, I'm sorry: forgot the extra qualification on the innermost SELECT:
> 
>  AND oldest.timestamp < history.timestamp

Hmmm ... I'll try both solutions tommorrow.  That is, I'll see if they port
 across databases ...
 
> > Yeah, you guessed it ... MS SQL Server 7.
> 
> I dunno, how good is SQL Server on subselects?

Not very good.  A lot of stuff, like subselects in the SELECT line, is not
 supported.    And MS has gotten further from the SQL standard with each update
 since SQL Server 7.0 ...

-Josh


______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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: UPDATE Query problem
  2002-01-18 00:55 UPDATE Query problem Josh Berkus <josh@agliodbs.com>
  2002-01-18 01:20 ` Re: UPDATE Query problem Tom Lane <tgl@sss.pgh.pa.us>
  2002-01-18 03:59   ` Re: UPDATE Query problem Josh Berkus <josh@agliodbs.com>
  2002-01-18 04:14     ` Re: UPDATE Query problem Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-01-18 23:02       ` Josh Berkus <josh@agliodbs.com>
  1 sibling, 0 replies; 7+ messages in thread

From: Josh Berkus @ 2002-01-18 23:02 UTC (permalink / raw)
  To: pgsql-sql

Tom, Stephan,

Well, you'll be interested to know that Stephan's solution worked for both
 PostgreSQL and MS SQL Server ... as far as parsing goes.  On PostgreSQL, the
 query took 14 minutes to complete.  

On MS SQL Server, it never completed at all.  Looks like I will have to take a
 semi-procedural approach with MS SQL Server after all.  Just another evidence
 of the superiority of Postgres ...

-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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: UPDATE Query problem
  2002-01-18 00:55 UPDATE Query problem Josh Berkus <josh@agliodbs.com>
@ 2002-01-18 01:20 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  1 sibling, 0 replies; 7+ messages in thread

From: Stephan Szabo @ 2002-01-18 01:20 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

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<history.period_date);

might work if really slow.





^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2002-01-18 23:02 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-01-18 00:55 UPDATE Query problem Josh Berkus <josh@agliodbs.com>
2002-01-18 01:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-01-18 03:59   ` Josh Berkus <josh@agliodbs.com>
2002-01-18 04:14     ` Tom Lane <tgl@sss.pgh.pa.us>
2002-01-18 05:18       ` Josh Berkus <josh@agliodbs.com>
2002-01-18 23:02       ` Josh Berkus <josh@agliodbs.com>
2002-01-18 01:20 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>

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