title: How to Update with a Self join?
body: <p>I have a postgres table, containing election results like this:</p>
  
  <pre><code>CREATE TABLE results
  (
    seats character varying(255),
    candidate character varying(255),
    party character varying(255),
    votes numeric(10,0)
    status character varying(2)
  )
  </code></pre>
  
  <p>I can get the names of the winners using the following query:</p>
  
  <pre><code>select r.seats, r.candidate, r.votes
  FROM results r
  join(
  select seats as s, max(votes) as mv
  FROM results
  group by seats) as a
  on r.seats=a.s AND r.votes=a.mv;
  </code></pre>
  
  <p>Now I wish to set the <code>status</code> column of all these winner to be equal to 'W', but I can't manage to write a SQL query to do this.</p>
  
  <p>I've tried this query, but it sets the <code>status</code> for all rows to be 'W':</p>
  
  <pre><code>update results set status='W'
  FROM results r
  join(
  select seats as s, max(votes) as mv
  FROM results
  group by seats) as a
  on r.seats=a.s AND r.votes=a.mv; 
  </code></pre>
  
  <p>How do I write an <code>Update..From</code> query to update only those rows containing the 'winners' I get by the first query?</p>