Update values in a column by incrementing a counter using SQL query

Question

I have a table structure like this :

-----------------------------------
ID  |    NAME   |   SORTED_ID
-----------------------------------
1   |    B      |      
2   |    D      |
3   |    A      | 
4   |    C      |

Using a single SQL query i want to update the SORTED_ID column like this, based on the sorted order.

-----------------------------------
ID  |    NAME   |   SORTED_ID
-----------------------------------
1   |    B      |      2
2   |    D      |      4
3   |    A      |      1
4   |    C      |      3

How can i do so?

this might help http://stackoverflow.com/questions/16847574/how-to-use-row-number-in-sqlite — Vamsi Prabhala, Oct 30 '15 at 14:13

score 0 · Answer 1 · answered Oct 30 '15 at 14:18

In SQLite, this is a bit painful. Here is one method using a correlated subquery:

update t
    set sorted_id = (select count(*)
                     from t t2
                     where t2.name <= t.name
                    );

Note: this is expensive on mediumish tables (those more than a thousand rows or so).

Update values in a column by incrementing a counter using SQL query

1 Answers1