Increment "sequence" column based on alphabetical order of another column

Question

Assume the following schema:

text|sequence
----|--------
Foo |       1
Bar |       2
Baz |       3

How would I UPDATE the sequence column so it incremented starting at 1 with no gaps based on the alphabetical order of the text column?

The resulting data would look like:

text|sequence
----|--------
Foo |       3
Bar |       1
Baz |       2

Answer 1

Try this:

UPDATE A 
SET A.sequene = B.sequence 
FROM tableA A 
INNER JOIN (SELECT text, ROW_NUMBER() OVER (ORDER BY text) sequence 
            FROM tableA 
           ) AS B ON A.text = B.text