Re: UPDATE Query problem

"Josh Berkus" <josh(at)agliodbs(dot)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