1

I got two tables, one of which is a helper of the other. The table "reviews" has a column which is an integer referencing to the id of the film of that review in the table "films". Is there a way to, after inserting in the table "films" a new film, use the last_insert_rowid in the table "reviews"?

I'm using SQLITE; I've seen uses with max(id) but it might not be as reliable. Help would be greatly appreciated.

user1264201
  • 65
  • 9

1 Answers1

3

You can just simply use it as a value in your next insert, à la

INSERT INTO films (name) values ('Shark!');
INSERT INTO reviews (filmId,review) values (last_insert_rowid(), 'Good!');

Demo here.

Thomas Tempelmann
  • 9,137
  • 6
  • 60
  • 120
Joachim Isaksson
  • 163,843
  • 22
  • 249
  • 272
  • Thanks! I thought it only worked for the same table. Also, interesting site SQL Fiddle! I dind't know it existed. – user1264201 Apr 02 '12 at 12:27
  • 1
    It's worth noting that last_insert_rowid() acts as a quasi-global value - it stores the rowid from the previous INSERT operation, across the current database connection. – Thomas Tempelmann Nov 25 '18 at 11:07