sqlite: reorder sort_order in table with sqlite

vendredi 27 novembre 2015

I have this table:

id  sort_ord
0   6
1   7
2   2
3   3
4   4
5   5
6   8

Why does this query:

UPDATE table
  SET sort_ord=(
    SELECT count(*)
    FROM table AS pq
    WHERE sort_ord<table.sort_ord
    ORDER BY sort_ord
  )
WHERE(sort_ord>=0)

Produce:

id  sort_ord
0   4
1   5
2   0
3   1
4   2
5   4
6   6

I was expecting all sort_ord fields to subtract by 2.

sqlite