0

is there a way ( in a single query ), to insert multiple rows, and have some rows know the id's of the previously inserted rows?

insert into my_table(contents, css) values ('some text', 'color: blue');  // grab id from that
insert into my_table(contents, css, parent_id) values ('child text', 'color: pink', last_insert_id);
// etc

It's a performance problem to separate them into separate queries, and I'd like to avoid using proc's, so is there a way to do this in mysql only?

dansch
  • 6,059
  • 4
  • 43
  • 59
  • Maybe this will help http://stackoverflow.com/questions/9046971/mysql-equivalent-of-oracles-sequence-nexval – lukpaw Nov 10 '13 at 21:53

2 Answers2

0

Try this:

insert into my_table(contents, css) values ('some text', 'color: blue');  // grab id from that

SELECT LAST_INSERT_ID();

insert into my_table(contents, css, parent_id) values ('child text', 'color: pink', last_insert_id);

SELECT LAST_INSERT_ID();
Pedro Werneck
  • 40,902
  • 7
  • 64
  • 85
Fransiscus Setiawan
  • 36
  • 3
  • this is untested but i see that i didn't accept an answer and I stopped working on this project a while ago – dansch Jan 24 '14 at 16:34
0

You can get the last inserted id, but not in a way that will allow you to insert multiple rows in a single query the way you want, since to get it requires a query anyway.

If there won't be a lot of concurrent access, you may lock the table, pre-compute the ids based on the current max id, insert everything and unlock.

Pedro Werneck
  • 40,902
  • 7
  • 64
  • 85