How to update a column for all rows by increment of 10

user_10196335 0
Viewed 39

I have a situation where there are column (int) named 'position' in table. I need to update that column by increment of 10 for every row. I can create a function for that, but can it be achieved by simple query?

Quick example of current values and desired ones:

| ID | POSITION | .... | WHAT I WANT POSITION TO BE |
   1         10    xxx                          10
   2         21    xxx                          20
   3         22    xxx                          30
   5         30    xxx                          40
.... etc
postgresql sql
1 Answers

You can use row_number() and arithmetic:

select t.*,
       10 * row_number() over (order by position) as new_position
from t;

If you want to update the value, then you can include this in an update:

update t
    set position = tt.new_position
    from (select t.*,
                 10 * row_number() over (order by position) as new_position
          from t
         ) tt
    where tt.id = t.id;
user_11440350
Related