CODEWITHSUNDEEP
sqlsqlite
user1095108
user1095108

Reputation: 14603

reorder sort_order in table with sqlite

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.

Upvotes: 0

Views: 244

Answers (1)

Fabio Duran Verdugo
Fabio Duran Verdugo

Reputation: 172

Here is defined: https://www.sqlite.org/isolation.html

About this link i can interpret, you has several instances for one query (update table and select count table) and independent of each other. When you are in update sort_data(5) id 5, you have new data for read on every "SET sot_ord" (understanding what say about isolation), and now the result is 4. Every select is a new instance and a new data reading

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

Upvotes: 1

Related Questions