Updating all data in one column in sqlite

Question

I have data in sqlite database. Every data has ID which is primary key when I delete data from database I want to resort ID of all data in a database. For example,

+----+--------+
| ID |  NAME  |
+----+--------+
| 01 | Taylor |
+----+--------+
| 02 |  Mark  |
+----+--------+
| 03 |  Bell  |
+----+--------+
| 04 |  Jane  |
+----+--------+

Then I remove 02 | Mark from a database and then a database must resort ID automatically.

+----+--------+
| ID |  NAME  |
+----+--------+
| 01 | Taylor |
+----+--------+
| 02 |  Bell  |
+----+--------+
| 03 |  Jane  |
+----+--------+

Thanks.

I don't know your use case, but changing the ID of records in the DB does not sound like a good idea. — Henry, Dec 30 '12 at 09:10
Objective of data in the database is just for show on the screen and user can delete data from database. PS. I just work with database for the first time. My method maybe wrong. You can give me the right method for this case. — aratn0n, Dec 30 '12 at 09:17
You don't show the ID to the users, do you? so it does not matter if they are not contiguous. Just use the ID internally in your program to identify a certain record. — Henry, Dec 30 '12 at 09:21

score 3 · Answer 1 · edited May 23 '17 at 11:43

Don't do that. Don't change IDs in your database just because you delete records. Even though SQLite is not a "real" database - don't do that.

Instead, you can fetch the row number while querying your database. Based on the answer here, you can do something like this:

SELECT id, name, 
    (SELECT COUNT(*) FROM table AS t2 
        WHERE t2.id<=t1.id) AS row_num
FROM table AS t1
ORDER BY id

NOTE: This query may become a performance issue if your table is large. Don't worry about it just yet, but keep it in mind.

Updating all data in one column in sqlite

1 Answers1