CODEWITHSUNDEEP
sqlpostgresql
Devdatta Tengshe
Devdatta Tengshe

Reputation: 4075

How to Update with a Self join?

I have a postgres table, containing election results like this:

CREATE TABLE results
(
  seats character varying(255),
  candidate character varying(255),
  party character varying(255),
  votes numeric(10,0)
  status character varying(2)
)

I can get the names of the winners using the following query:

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;

Now I wish to set the status column of all these winner to be equal to 'W', but I can't manage to write a SQL query to do this.

I've tried this query, but it sets the status for all rows to be 'W':

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;

How do I write an Update..From query to update only those rows containing the 'winners' I get by the first query?

Upvotes: 1

Views: 73

Answers (1)

roman
roman

Reputation: 117465

update results as r set
    status = 'W'
from (
    select t.seats, max(t.votes) as max_votes
    from results as t
    group by t.seats
) as a
where a.seats = r.seats and a.max_votes = r.votes;

Upvotes: 1

Related Questions