CODEWITHSUNDEEP
sqlitecordovainsertauto-incrementweb-sql
Phil Barnett
Phil Barnett

Reputation: 73

sqlite - using last_insert_rowid() with multiple inserts

I'm trying to insert record a record to my 'quote_link' table using last_insert_rowid() to get the id from the newly created record in the 'quote' table. Then I'm using this as the value to insert into into ql_quote_id column in the 'quote_link' table.

This works

tx.executeSql( 'INSERT INTO quotelink(ql_quote_id,ql_cat_id,ql_kind_id) Values (last_insert_rowid(),?,?)', [cat_id,kind_id],

however, i am doing a number of such insets and only the first one gets the correct value for ql_quote_id (for the second insert the value of last_insert_rowid() gets the id from first insert)

I'm thinking I need to do a select sub query but I haven't a clue what the syntax would

Upvotes: -1

Views: 575

Answers (2)

Serg Stetsuk
Serg Stetsuk

Reputation: 429

I use the following pure SQLite approach (it looks like SQLite does not support variables directly):

BEGIN TRANSACTION;
INSERT INTO quote (...) VALUES(...);
INSERT INTO quote_link (ql_quote_id,...) VALUES (last_insert_rowid(),...);
INSERT INTO quote_link (ql_quote_id,...) VALUES ((SELECT ql_quote_id FROM quote_link WHERE id=last_insert_rowid()),...);
COMMIT TRANSACTION;

Thus the first insert into quote table is pretty common. The first insert into quote_link table uses just inserted last_insert_rowid(). All the rest inserts into quote_link table use previously inserted ql_quote_id value from previous inserts into quote_link table.

This way you eliminate the need to use temporary tables for storing kind of variables and other headaches.

Upvotes: 0

CL.
CL.

Reputation: 180240

The SQLResultSet of the result set callback allows you to get that ID:

tx.executeSql('INSERT INTO quote(...) VALUES(...)',
              [...],
              function(tx, result) {
    var quote_id = result.insertId;
    tx.executeSql('INSERT INTO quotelink(...) VALUES(?,?,?)',
                  [quote_id, cat_id, kind_id]);
});

Upvotes: 0

Related Questions