CODEWITHSUNDEEP
sqlsqliteinsertauto-increment
Dima Tisnek
Dima Tisnek

Reputation: 11779

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?

Upvotes: 12

Views: 12932

Answers (7)

Y.B.
Y.B.

Reputation: 3586

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.

Upvotes: 7

Sameyo
Sameyo

Reputation: 26

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

Upvotes: 0

Somnath Muluk
Somnath Muluk

Reputation: 57796

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

Upvotes: 3

Muhammad Muazzam
Muhammad Muazzam

Reputation: 2800

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.

Upvotes: -1

Josh Edwards
Josh Edwards

Reputation: 908

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';

Upvotes: 0

Steven
Steven

Reputation: 911

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

id  bar
1   1
2   2
3   3
4   4

Upvotes: 1

Othman Dahbi-Skali
Othman Dahbi-Skali

Reputation: 632

INSERT INTO foo (bar) VALUES (id)

Upvotes: -2

Related Questions