2

I want to insert one record in each table from one select.

For example, insert in table "messages" and table "notifications" from table "users".

"notifications" need the LAST_INSERT_ID() to point to the message.

I can achieve one table like:

INSERT INTO message (`data1`, `data2`) SELECT "text", u.id FROM users u WHERE 1;

But don't know where to put the INSERT INTO for "notifications".

chris85
  • 23,846
  • 7
  • 34
  • 51
Alexis Moreno
  • 43
  • 4
  • If you are looking for to have an atomic operation (Insert the information in both tables or nothing), you should use a transaction. https://dev.mysql.com/doc/refman/5.0/en/commit.html – CrApHeR May 18 '15 at 02:54
  • Execute the first insert, pull the last inserted id from that, then run the second insert with that ID. http://php.net/manual/en/mysqli.insert-id.php – chris85 May 18 '15 at 02:56
  • Check this, it may help http://stackoverflow.com/questions/7482443/how-to-copy-data-from-one-table-to-another-new-table-in-mysql – Elyor May 18 '15 at 03:01
  • @CrApHeR I tried this but the select just inserted one record in both tables, the select from the "users" table return about 10 rows for example, then the transaction should insert 10 rows in "messages" and 10 rows in "notifications", can I use the transaction for something like that? still I learned some now. Thanks! – Alexis Moreno May 18 '15 at 03:14
  • @Elyor I think is the same script I got, I'm missing the part to insert to another table with the same select. Thanks. – Alexis Moreno May 18 '15 at 03:16
  • @CrApHeR to expand my test this is what I did: `START TRANSACTION; SELECT @U_ID:=u.id FROM users u WHERE 1; INSERT INTO message ('data1', 'data2') VALUES ("text", @U_ID); INSERT INTO notificacion ('data1', 'data2') VALUES (@E_ID, LAST_INSERT_ID()); COMMIT;` – Alexis Moreno May 18 '15 at 03:18
  • Some interesting approaches can be found here: http://stackoverflow.com/questions/5178697/mysql-insert-into-multiple-tables-database-normalization – Axalix May 18 '15 at 03:29
  • @Axalix I understand now I can't insert with one SQL command, but a transaction works for me as CrApHeR example, how can I make the SELECT work with TRANSACTION for multiple inserting? – Alexis Moreno May 18 '15 at 03:46

0 Answers0