Danilo Calzetta Danilo Calzetta - 6 months ago 7
SQL Question

update column with incremental value

i have a table like this

userid | points | position
1 | 100 | NULL
2 | 89 | NULL
3 | 107 | NULL


i need a query for update the position column ordering by points desc, example result:

userid | points | position
1 | 100 | 2
2 | 89 | 3
3 | 107 | 1

Answer

I would not use physical columns that depend on values in other rows, otherwise you have to update the entire table every time one row changes. Use a view or other mechanism to calculate the position on the fly.

The query to calculate "position" would look something like:

SELECT 
    userid, 
    points, 
    RANK() OVER (ORDER BY points DESC) AS position

However, if you have to make it an UPDATE then you could use something like

UPDATE a
SET a.position = b.position
FROM {table_name} a
INNER JOIN
(
    SELECT 
        userid, 
        RANK() OVER (ORDER BY points DESC) AS position
        FROM {table_name}
) b
ON a.userid = b.userid

but keep in mind that you will need to run the update every time the table is updated, so performance may be an issue if it's a decent size table that gets updated a lot.