sql - 如何通过自我加入进行更新？

我有一个postgres表，包含这样的选举结果：

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

我可以通过以下查询获得获奖者的姓名：

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;

现在我想将所有这些获胜者的status列设置为'W'，但我无法编写一个SQL查询来完成此操作。
我试过这个查询，但它将所有行的status设置为“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;

如何编写Update..From查询以仅更新包含第一个查询获得的“winners”的行？

最佳答案

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;