使用rank()更新PostgreSQL表

Question

我试图用rank()的结果更新postgresql表中的一列pop_1_rank，操作如下：

UPDATE database_final_form_merge
SET    
    pop_1_rank = r.rnk
FROM (
    SELECT pop_1, RANK() OVER ( ORDER BY pop_1 DESC) FROM database_final_form_merge WHERE territory_name != 'north'  AS rnk)r

单独使用SELECT查询是可以正常工作的，但我无法正确地进行更新。我在这里做错了什么？

- jonmrich

---

SELECT语句中没有FROM子句。这怎么可能正常工作呢？ - Giorgos Betsos

@GiorgosBetsos 抱歉...我在转换为“伪代码”时漏掉了一些东西。 - jonmrich

开始时，您的别名应该在 RANK() OVER() as rnk 之后，而不是在 WHERE 之后。 - Juan Carlos Oropeza

Answer 1

我更喜欢使用CTE表示法。

WITH cte as (
     SELECT pop_1, 
            RANK() OVER ( ORDER BY pop_1 DESC) AS rnk
     FROM database_final_form_merge 
     WHERE territory_name <> 'north'  
)
UPDATE database_final_form_merge 
SET pop_1_rank = cte.rnk
FROM cte
WHERE database_final_form_merge.pop_1 = cte.pop_1

- Juan Carlos Oropeza

Answer 2

据我所知，Postgres 更新的是表而不是子查询。因此，您可以连接回表：

UPDATE database_final_form_merge
    SET    pop_1_rank = r.rnk
    FROM (SELECT pop_1, RANK() OVER ( ORDER BY pop_1 DESC) as rnk
          FROM database_final_form_merge
          WHERE territory_name <> 'north'
         ) r
    WHERE database_final_form_merge.pop_1 = r.pop_1;

此外：

• 列别名使用列名。
• 这假设pop_1是连接两个表的id。

- Gordon Linoff

Answer 3

你的 UPDATE 查询缺少 WHERE 子句，因为在使用 UPDATE ... FROM 时，实际上是在进行连接操作。

所以你需要选择主键，然后根据主键匹配来更新只计算排名的列。

- hruske

---

好的...我对这个还有点陌生，你能给我一些指导吗？ - jonmrich