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 = 0from (select min(datetime) as datetime, membid from sessions2 group by
membid)as mindatewhere 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
informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small
businesses fax 621-2533 and non-profit organizations. San Francisco
