qarma qarma - 1 year ago 52
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 Source

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.