title: Update a column based on row_number()
body: <p>i created a new not-null column with default value 0 for my table and it keeps display orders. I want to update all rows for that table, that displayorder has the value of row_number() over id ordered. here i can do this for one id. How can i do that for all ids.</p>
  
  <p>my table is: </p>
  
  <pre><code>id | personid | name   | displayorder
  ---+----------+--------+------------
   1 |    10    | test1  |    0
   2 |    10    | test2  |    0
   3 |    10    | test3  |    0
   4 |    10    | test4  |    0
   5 |    10    | test5  |    0
   6 |    11    | test6  |    0
   7 |    11    | test7  |    0
   8 |    12    | test8  |    0
  </code></pre>
  
  <p>i want the result is:</p>
  
  <pre><code>id | personid | name   | displayorder
  ---+----------+--------+------------
   1 |    10    | test1  |    1
   2 |    10    | test2  |    2
   3 |    10    | test3  |    3
   4 |    10    | test4  |    4
   5 |    10    | test5  |    5
   6 |    11    | test6  |    1
   7 |    11    | test7  |    2
   8 |    12    | test8  |    1
  </code></pre>
  
  <p>here is my sql code, but it only works for just one given id:</p>
  
  <pre><code>update MyTable
  set displayorder = z.ord
   FROM  (
     SELECT row_number() over (order by id) as ord, id 
       FROM MyTable p2
     where p2.personid = 1
     ) z
    where MyTable.id= z.id
    and personid = 1
  </code></pre>