hiy hiy - 5 months ago 8
SQL Question

Retrieving autoincrement column after insert in SQLite3

I have a table in SQLite3 with the following schema:

create table threads(
id integer primary key autoincrement,
submitter text,
body text,
title text,
subtime int -- Unix time
);


I'm inserting rows like this:

insert into threads (title, body, subtime, submitter) values
("I like ducks", "Don't you?", 1467664977640, "tom");


I'd like to to get the id column after I insert the thread. How can I do this? Ideally I could insert and retrieve the column in the same statement.

Answer

I don't know if you can do that in one statement but immediately after the insert statement you can use the following statement to get the last auto incremented id:

SELECT last_insert_rowid()

Another way is to use the following statement:

select seq from sqlite_sequence where name="threads"