Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0I1K5V62859 for ; Thu, 17 Jan 2002 20:20:05 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g0I1K0301982; Thu, 17 Jan 2002 20:20:00 -0500 (EST) To: "Josh Berkus" cc: pgsql-sql@postgresql.org Subject: Re: UPDATE Query problem In-reply-to: References: Comments: In-reply-to "Josh Berkus" message dated "Thu, 17 Jan 2002 16:55:43 -0800" Date: Thu, 17 Jan 2002 20:20:00 -0500 Message-ID: <1979.1011316800@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200201/211 X-Sequence-Number: 6296 "Josh Berkus" 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