CODEWITHSUNDEEP
c++sqldatabasesqlite
Birdbuster
Birdbuster

Reputation: 1549

How to reindex or fix rowid after a row is deleted sqlite?

Alright I am in a rather difficult situation, or at least I think so anyway. I have been doing some research on how to fix my problem but have really come up empty handed.

I need to be able to reindex the rowid of my table after I delete a row. That way at any given time when I want to update or index a row by the rowid it is accessing the correct one.

Now for those of you asking why. Basically I am interfacing a "homebrewed" db that was programmed in C and is really just a bunch of memory locations all accessed like they were a db table. So what I'm trying to say is they can look up a row by searching for a value in the table, or by simply saying i want row 6. Lastly the table could consist of really anything, and any values which means they dont create a column as an index and ultimately the only thing for me to index their row by row number is the rowid to my knowledge.

So I have found that VACUUM would do what I want or need but it appears that the system that database is in isn't giving sqlite privileges to write so when VACUUM is run it comes back with and error. (ERROR 14 or Unable to open the database file) (I also know that my db is open so that isn't the issue but not having write privileges is the only reason I can come up with) I have also read some stuff about the auto increment or something like that but didn't really understand/think that was going to be able to fix my problem.

Any suggestions or ideas from the sqlite or database geniuses out that would be appreciated.

Upvotes: 0

Views: 1038

Answers (1)

mucio
mucio

Reputation: 7119

Not sure if I have understood completely your problem, but if you can use SQL code maybe you can write a query to update the IDs (assuming they are in dense order).

You can use a query like this:

UPDATE t1
SET id = (SELECT rank 
                 FROM (SELECT id, 
                              (
                                  SELECT count()+1 
                                    FROM (SELECT DISTINCT id 
                                            FROM t1 AS t
                                           WHERE t.id < t1.id
                                         ) 
                              ) rank
                         FROM t1
                       ) AS sub
 WHERE sub.id = t1.id
);

You can check my demo in SQLFiddler. In this demo you will see the result of the DELETE and UPDATE statements (to simulate your case) if you run all queries together.

Upvotes: 2

Related Questions