Danilo Calzetta Danilo Calzetta - 1 year ago 42
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 Source

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:

    RANK() OVER (ORDER BY points DESC) AS position

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

SET a.position = b.position
FROM {table_name} a
        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.