sql - 如何使用自连接进行更新？

Question

我有一个 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 查询来仅更新那些包含我通过第一个查询获得的“获胜者”的行？

Answer 1

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;