CODEWITHSUNDEEP
sqlsqlite
SaiyanRiku
SaiyanRiku

Reputation: 337

SQLite Insert with the newly generated id

I have a table with an auto increment field and another field VARCHAR filed. I would like to fill the string field with the newly generated id directly in the insert query. For example with table:

Person
(
    id auto_increment, 
    name varchar
)

I would like to perform a query like this:

INSERT INTO Person(name) VALUE ('Person #' + ROWID())

Anyway to do this?

Upvotes: 3

Views: 1425

Answers (5)

Prix
Prix

Reputation: 19528

This will use the last inserted row id with the increment of 1 to generate the next person id. Keep in mind that for last_insert_rowid() you should not be doing concurrent inserts to the database.

INSERT INTO Person (name) SELECT 'Person #' || (last_insert_rowid()+1);

Alternatively this will read the biggest ID you currently have and increment +1 to it and then concatenate it with the text. If it returns null it uses 1.

INSERT INTO Person (name) SELECT 'Person #' || (IFNULL((MAX(id)+1),1)) FROM Person LIMIT 1;

This one will get the index of autoincrement:

INSERT INTO Person (name) SELECT 'Person #' || (seq+1) FROM sqlite_sequence WHERE name="person"

You can also do something like this:

INSERT INTO Person (name) VALUES (NULL);
INSERT INTO Person (name) VALUES (NULL);
INSERT INTO Person (name) VALUES (NULL);
INSERT INTO Person (name) VALUES (NULL);
INSERT INTO Person (name) VALUES (NULL);
INSERT INTO Person (name) VALUES (NULL);
INSERT INTO Person (name) VALUES (NULL);
INSERT INTO Person (name) VALUES (NULL);
UPDATE Person SET name = 'Person #' || id WHERE name IS NULL;

Or:

INSERT INTO Person (name) VALUES ('Person');
UPDATE Person SET name = name || ' #' || id WHERE id = last_insert_rowid();

This one will update the names that does not have a # with its row id:

INSERT INTO Person (name) VALUES ('Person');
INSERT INTO Person (name) VALUES ('Person');
INSERT INTO Person (name) VALUES ('Person');
INSERT INTO Person (name) VALUES ('Person');
UPDATE Person SET name = name || ' #' || id WHERE (LENGTH(name)-LENGTH(REPLACE(name, '#', ''))) = 0;

Live DEMO

Upvotes: 3

MystikSpiral
MystikSpiral

Reputation: 5028

The system will not reliably know that ID until the row is inserted. I would think the best solution would be a view (since SQLite does not support computed columns which would be the easiest route). Just insert the VARCHAR and use a view to build the concatenated value at request time using the inserted VARCHAR and the allocated ID.

Good Luck!

Upvotes: 1

Dhaval
Dhaval

Reputation: 2861

This will not be guranteed but this might work ...

INSERT INTO tab1(val) VALUES ('tab1 #' + cast((SELECT top 1 IDENT_CURRENT('tab1') + IDENT_INCR('tab1') FROM tab1) as varchar(20)))

Upvotes: 0

Mischa
Mischa

Reputation: 43308

You shouldn't store the same info twice. Just generate the string you want dynamically when you query:

SELECT name || '#' || id FROM Person

Upvotes: 2

Miguel Prz
Miguel Prz

Reputation: 13792

You have to do two sentences: INSERT + UPDATE, you cannot access the last insert id in the current insert sentence

Upvotes: 1

Related Questions