SQLite Forum



(1) By nyl (nylink) on 2021-09-12 15:33:55 [link]

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 ?

(2.1) By Larry Brasfield (larrybr) on 2021-09-12 19:11:16 edited from 2.0 in reply to 1 [link]

Look at the <u>[RETURNING clause](https://sqlite.org/lang_returning.html)</u>.

Or, for versions of SQLite too old to support RETURNING: <code>
 create table EverMore(id integer primary key, stuff text);
 insert into EverMore values
 (null, 'One'); select last_insert_rowid() as id;

(3) By nyl (nylink) on 2021-09-12 20:17:39 in reply to 2.1 [link]

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

(4) By Tim Streater (Clothears) on 2021-09-12 21:24:22 in reply to 3 [link]

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;

(5) By anonymous on 2021-09-13 17:19:11 in reply to 4

thanks a lot!

(6) By Trudge on 2021-09-14 16:09:53 in reply to 4 [link]

What an excellent piece of coding. I can figure several ways I can use this immediately. Thank you.