qarma qarma - 1 month ago 9
SQL Question

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?

Answer

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.