Search code examples
sqlsqliteinsertauto-increment

Insert into a table and set another column to autoincremented column value

Let's say I have a simple table:

create table foo
{
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    bar INTEGER
}

And I want to insert a new row, such that id == bar where value for id is chosen by the database, a.k.a. auto-increment.

Something like this:

INSERT INTO foo (id, bar) VALUES (NULL, id)

Is it possible do this in one statement?

What is the SQL syntax for that?

Solution

  • In SQLite you can

    BEGIN TRANSACTION;
  INSERT INTO foo (id, bar) VALUES (NULL, 0);
  UPDATE foo SET bar = id WHERE _ROWID_ = last_insert_rowid();
COMMIT;

    to make sure no other statement gets in the way of your two-statement expression.