hiy hiy - 1 year ago 100
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 Source

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"
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download