Re: "How do I ..." SQL question - Mailing list pgsql-sql

Hi there:

Thank you for the response, which gave me what I wanted.
Here is a follow-up question..

First a recap:

Table a contains the names of individuals, the places
they have visited and the year in which they were visited.

Let's see who has visited where and when:

SELECT * FROM a;
name   place   year
------ ------- ------kim    north   2004kim    south   2003kim    south   2003bob    west    2004bob    west    2004bob
  west    2003joe    south   2004joe    south   2005sue    west    2004bob    east    2003joe    east    2004joe
east   2004sue    south   2004bob    north   2004bob    north   2005
 

Summarize data by number of places visited by year:

SELECT count(*) AS count, name, year FROM a GROUP BY name, year ORDER BY count DESC, name ASC;
count   name   year
------- ------ ------  3     bob    2004  3     joe    2004  2     bob    2003  2     kim    2003  2     sue    2004  1
   bob    2005  1     joe    2005  1     kim    2004
 

Return only four rows beginning at second row into temporary table:

CREATE TEMPORARY TABLE output AS  SELECT count(*) AS count, name, year FROM a   GROUP BY name, year   ORDER BY count
DESC,name ASC   LIMIT 4 OFFSET 1;
 

SELECT * FROM output;
count   name   year
------- ------ ------  3     joe    2004 s,e,e  2     bob    2003 w,e  2     kim    2003 s,s  2     sue    2004 s,w

Select only places visited included in LIMITed query:

SELECT DISTINCT a.place FROM a, output WHERE a.name=output.name AND a.year=output.year;
place    
-------southwesteast


Here is the new question.  I want to use the above result to update
another table which contains unique places visited and also has a field
to indicate recently visited places already present.

SELECT * FROM places;
refresh   place
--------- -------   0      south   0      west   0      southwest

(The following two queries do not work right and are what I need help with)

Add new places:

INSERT INTO places (refresh, place) SELECT DISTINCT 1, a.place FROM a, output   LEFT JOIN places ON
places.place=a.place  WHERE a.name=output.name AND a.year=output.year     AND places.place IS NULL;
 

Update refresh flag for existing places.  Note: the refresh field
can have one of several values and I only want to change it when
it has a particular value.

UPDATE places SET refresh=1 FROM output, a WHERE places.refresh=0   AND places.place=a.place   AND a.name=output.name
ANDa.year=output.year;
 

(The last query never updates the places tableand I'm not sure how to do this)

I want this result:

SELECT * FROM places;
refresh   place
--------- -------   1      south   1      west   0      southwest   1      east

Any help appreciated.

-Bob