Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8LGLle42839 for ; Fri, 21 Sep 2001 12:21:47 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 123339; Fri, 21 Sep 2001 09:21:45 -0700 From: "Josh Berkus" Subject: Re: select is fast, update based on same where clause is slow To: "Jeff Barrett" Cc: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Fri, 21 Sep 2001 09:21:45 -0700 Message-ID: In-Reply-To: <9ofjt8$177a$1@news.tht.net> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200109/185 X-Sequence-Number: 4867 Jeff, I think that you're running into a syntactical problem here: > Then I issue an update as follows (to update those 74,000+ rows): > update sessions2 set sinceinception = 0 > from sessions2 s, (select min(datetime) as datetime, membid from > sessions2 > group by membid) as mindate > where s.membid = mindate.membid > and s.datetime = mindate.datetime; In the query above, there is no linkage between the instance of sessions2 (sessions2) you are updating and the instance you are limiting (s). As a result, you are running an update on 74,000^2 rows (about 55 trillion), and if the query ever completed you would find that sinceinception would be set to 0 for all rows in sessions2. Your query should read: update sessions2 set sinceinception = 0 from (select min(datetime) as datetime, membid from sessions2 group by membid) as mindate where sessions2.membid = mindate.membid and sessions2.datetime = mindate.datetime; It's a peculiarity of Update queries that the name of the updated table cannot be aliased anywhere in the query. To add to the confusion, in MS Transact SQL, you are expected to repeat the name of the updated table in the FROM clause, while in Postgres such repetition is prohibited. This is mainly due to the fact that UPDATE ... FROM is not well-defined in the SQL 92 standard. -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