CODEWITHSUNDEEP
postgresql
jonmrich
jonmrich

Reputation: 4323

Update Postgresql table using rank()

I'm trying to update a column (pop_1_rank) in a postgresql table with the results from a rank() like so:

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

The SELECT query by itself works fine, but I just can't get it to update correctly. What am I doing wrong here?

Upvotes: 3

Views: 2653

Answers (3)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

I rather use the CTE notation.

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

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1269753

As far as I know, Postgres updates tables not subqueries. So, you can join back to the table:

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;

In addition:

  • The column alias goes by the column name.
  • This assumes that pop_1 is the id connecting the two tables.

Upvotes: 1

hruske
hruske

Reputation: 2253

You're missing WHERE on UPDATE query, because when doing UPDATE ... FROM you're basically doing joins.

So you need to select primary key and then match on primary key to update just the columns are computing rank over.

Upvotes: 0

Related Questions