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

Question

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?

why would you need the `bar` column at all? if it would always be the same as `id`? — Vamsi Prabhala, Dec 15 '15 at 14:50
How it's done is dependent on specific DB server, but I'm guessing that it would require a trigger to do this, triggering after the row is inserted and setting bar column to value from id column — DBug, Dec 15 '15 at 14:52
@vkp I want `id==bar` initially, and eventually `bar` may be changed. — Dima Tisnek, Dec 17 '15 at 11:32

score 7 · Accepted Answer · edited Oct 26 '16 at 15:40

7

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.

edited Oct 26 '16 at 15:40

radtek

34,210
11
144
111

answered Dec 18 '15 at 13:04

Y.B.

3,526
14
24

OT: did you test this with competing transactions? As in, is `last_insert_rowid()` reliable in this case? – Dima Tisnek Dec 21 '15 at 10:15
@qarma I have not, but according to SQLite documentation `Insert` would create `RESERVED` lock on the database file to ensure no other database write at all can happen until the end of our transaction. Thus no other thread should be able to affect `last_insert_rowid()`. Of cause there still can be all sorts of situations that might affect `last_insert_rowid()` and even the very fact of `Insert`, like `WITHOUT ROWID` table clause or `INSTEAD OF` trigger. I am afraid the solution must be tested on a particular database, but in principle - that's what transactions are for. – Y.B. Dec 21 '15 at 12:15

score 3 · Answer 2 · edited May 23 '17 at 12:19

3

You can't have two auto increment fields. You should use a single auto increment field. Given that both fields would always have the same value for every row, there's no reason to have to such fields anyway.

But you can just make trigger which will update another field equal to auto incremented value after inserting row. And delete that trigger when you don't want them to have same values.

CREATE TRIGGER update_foo AFTER INSERT ON foo 
  BEGIN
    UPDATE foo SET bar = NEW.id ;
  END;

When eventually bar will be changed to have not same value as id, then delete trigger

DROP TRIGGER update_foo

edited May 23 '17 at 12:19

Community

1
1

answered Dec 17 '15 at 17:34

Somnath Muluk

55,015
38
216
226

Since your trigger is on insert, I don't believe that you would need to delete it when bar needs to be updated. – Josh Edwards Dec 17 '15 at 18:02
@JoshEdwards: I mean when you don't want to have same value for both then delete trigger. – Somnath Muluk Dec 18 '15 at 05:19
@Y.B. : Updated answer. 1. INSERT : NEW references are valid 2. UPDATE : NEW and OLD references are valid 3. DELETE : OLD references are valid – Somnath Muluk Dec 22 '15 at 12:10

score 1 · Answer 3 · answered Dec 17 '15 at 19:36

This isn't done in one query, but it could be used within a stored procedure. The set is repeated to show that it does insert and update based upon the database created ID. This was done on SQL Server 2008R2

declare @tmpTable TABLE (
    id INT identity(1,1),
    bar INT
    )

    declare @myId INT
    insert @tmpTable (bar) values (0)
    SET @myId = SCOPE_IDENTITY() 
    update @tmpTable SET bar = @myId where id = @myId 

    insert @tmpTable (bar) values (0)
    SET @myId = SCOPE_IDENTITY() 
    update @tmpTable SET bar = @myId where id = @myId 


    insert @tmpTable (bar) values (0)
    SET @myId = SCOPE_IDENTITY() 
    update @tmpTable SET bar = @myId where id = @myId 


    insert @tmpTable (bar) values (0)
    SET @myId = SCOPE_IDENTITY() 
    update @tmpTable SET bar = @myId where id = @myId 

    select * FROM @tmpTable

OUTPUT

Josh Edwards · Answer 4 · 2015-12-17T19:39:02.050

0

As was mentioned in the comments, I believe that this will be specific to each database implementation, so it'd be useful to know which server you're using. However, for MySQL, you could do something like this:

INSERT INTO foo (bar) 
  SELECT AUTO_INCREMENT 
  FROM information_schema.tables 
  WHERE table_schema = DATABASE() 
    AND TABLE_NAME = 'foo';

Or, since you're probably using SQLite (based on the tag), you could try this:

INSERT INTO foo (bar) 
  SELECT (seq + 1)
  FROM sqlite_sequence
  WHERE name = 'foo';

edited Dec 17 '15 at 19:39

answered Dec 17 '15 at 17:52

Josh Edwards

888
5
13

Would this be reliable with competing transactions? As in is `sqlite_sequence` considered to be part of transaction? And would there not be a case where value for `id` would be `+1` in one transaction and `+2` in another? – Dima Tisnek Dec 21 '15 at 10:16
I believe that it will be reliable, as you don't run into any issues with having the same ID reused, and the db is internally using the sqlite_sequence, but I would recommend testing it out to verify. Not sure what you mean by the +1 in one transaction and +2 in another. It is always +1 because the sequence will get updated after the insert, so the plus one gives you the matching one. – Josh Edwards Dec 21 '15 at 18:41

score 0 · Answer 5 · answered Dec 22 '15 at 19:18

0

insert into [Test].[dbo].[foo] (bar) select MAX(id)+1 from [Test].[dbo].[foo]

answered Dec 22 '15 at 19:18

Sameyo

26
4

Muhammad Muazzam · Answer 6 · 2015-12-22T11:27:12.490

-1

You can use it as:

INSERT INTO foo (bar) VALUES (last_insert_rowid()+1)

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.

edited Dec 22 '15 at 11:27

answered Dec 20 '15 at 10:10

Muhammad Muazzam

2,810
6
33
62

Are you just guessing that `id` will also be set to `LAST_INSERT_ID()+1`? Wouldn't this fail in case of competing transactions? That is, one transaction will get `+1` and another `+2`? – Dima Tisnek Dec 21 '15 at 10:14
You can control this behavior by adding unique constraint at both columns and run this query again if fails. Second part should be done through script – Muhammad Muazzam Dec 21 '15 at 10:50
Only works if the last insert made on your current connection was also into foo. What do you think will happen if you haven't inserted anything since openeing the connection, or if the last insert was into a different (auto-increment) table?!? – Doin May 13 '21 at 19:11
https://stackoverflow.com/questions/36573707/fetching-last-insert-id-shows-wrong-number – Muhammad Muazzam May 17 '21 at 05:39

score -2 · Answer 7 · answered Dec 17 '15 at 17:49

-2

INSERT INTO foo (bar) VALUES (id)

answered Dec 17 '15 at 17:49

Othman Dahbi-Skali

592
8
5

This inserts a value of 0 for bar. – Josh Edwards Dec 17 '15 at 17:53
but the table creation script must be something like that : create table foo ( ID int IDENTITY(1,1) PRIMARY KEY, bar INTEGER ) – Othman Dahbi-Skali Dec 18 '15 at 09:36
This entered a 0 for me when I tried it in MySQL, and did not run at all when I tried it in Sqlite – Josh Edwards Dec 18 '15 at 14:18
okay, my code was for sql server, in mysql u can use : create table foo ( ID int NOT NULL AUTO_INCREMENT, bar int, PRIMARY KEY (id) ) --for example INSERT INTO foo (bar) VALUES (12) – Othman Dahbi-Skali Dec 18 '15 at 14:49

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

7 Answers7