I am looking to store a counter value that needs to be incremented from time to time
I have a table called
file_number with a single field called
num and just one record
to increment the counter I update the record:
UPDATE file_number SET num = num + 1
then to retrieve the value I do:
SELECT num FROM file_number LIMIT 1
is there a faster way to increment the value of the record and retrieve the value of it ?
is it efficient to store counters like this ?
Look at the RETURNING clause.
Or, for versions of SQLite too old to support RETURNING:
create table EverMore(id integer primary key, stuff text);
insert into EverMore values
(null, 'One'); select last_insert_rowid() as id;
thank you, in this last example insert adds a record to the DB when I would need to keep only one value and increment it
Create the table with one column, your id. Then insert one row setting id to its initial value. All of that is initialisation for your app.
Then when you come to use it, just do:
update file_number set num = num + 1 returning num;