Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0I3xPV93545 for ; Thu, 17 Jan 2002 22:59:25 -0500 (EST) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.5.2) with HTTP id 622662; Thu, 17 Jan 2002 19:59:27 -0800 From: "Josh Berkus" Subject: Re: UPDATE Query problem To: Tom Lane , "Josh Berkus" Cc: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.5.2 Date: Thu, 17 Jan 2002 19:59:27 -0800 Message-ID: In-Reply-To: <1979.1011316800@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200201/215 X-Sequence-Number: 6299 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