Postgresql: Update column1 with ascending values, depending on column2 sort order

Question

I have a table 'bride' with some string values like that:

id       name     order
-------  -------  -------
1        Beatrix 
2        Bill 
3        ORen
4        Vernita
5        Elle 
6        Budd

I would like to update the column 'order' with the numbers (starting with 1) according to the order of sorting the 'name' column. Like that:

id       name     order
-------  -------  -------
1        Beatrix  1
2        Bill     2
3        ORen     5
4        Vernita  6
5        Elle     4
6        Budd     3

My problem is how to use the sorted names as the order of inserting the actual order numbers. Any help would be great.

Answer 1

This can be done using a window function:

update the_table
  set "order" = t.rn
from (
   select id, 
          row_number() over (order by name) as rn
   from the_table
) t where t.id = the_table.id;

---

order is a reserved keyword. You should not use that as a column name.

Answer 2

You should be able to use a window function for this: http://www.postgresql.org/docs/9.1/static/tutorial-window.html

Something like this (I don't have postgres installed so I've tried this in SQL Server; it should be very similar if not the same):

update bride set [order] = sort
from (select id, name, rank() over (order by name) sort
from bride) o
where bride.id = o.id