0

I have two tables: message and message_content:

message
id | customer_id | creation_time | msg_headline | status | order_id 

message_content
message_id | msg | creation_time | employee_id

When I insert new data I need to create a record in both tables (linked by id resp. message_id). Is it possible to achieve that using a single statement?

Micha Wiedenmann
  • 19,979
  • 21
  • 92
  • 137
Mac Luc
  • 971
  • 5
  • 14
  • 31
  • 3
    That's not possible, you have to create 2 statements. – Denys Séguret Apr 24 '14 at 14:33
  • possible duplicate of [SQL Server: Is it possible to insert into two tables at the same time?](http://stackoverflow.com/questions/175066/sql-server-is-it-possible-to-insert-into-two-tables-at-the-same-time) –  Apr 24 '14 at 14:34
  • 1
    You can create a procedure that accepts all the data you need to insert into the two tables, and do two `INSERT` statements from it. But it is not possible to insert into 2 tables from one statement. – Siyual Apr 24 '14 at 14:34
  • 1
    You could do the two inserts in one transaction so they take effect at the same time (assuming the right storage engine). – Gordon Linoff Apr 24 '14 at 14:37
  • The possible duplication talks about SQL Server, this question is about MySQL. – Micha Wiedenmann Apr 24 '14 at 14:38
  • Please explain why you need a single statement? – Micha Wiedenmann Apr 24 '14 at 14:38
  • I thought it was possible, so i just wanted to know slick way to do it :) – Mac Luc Apr 24 '14 at 14:44

2 Answers2

1

It is not possible to insert into two tables with a single INSERT statement. However there are a few options. One of which is using LAST_INSERT_ID()[1]:

INSERT INTO `message` (`customer_id`, `creation_time`, `msg_headline`, `status`, `order_id`)
  VALUES (...);

INSERT INTO `message_content` (`message_id`, `msg`, `creation_time`, `employee_id`)
  VALUES (LAST_INSERT_ID(), 'message', default, 1)

In your actual implementation you should however probably switch to a prepared statement (and PHP: Prepared Statements - Manual, since you originally mentioned PHP).

Micha Wiedenmann
  • 19,979
  • 21
  • 92
  • 137
0

MySQL doesn't support multi-table insertion in a single INSERT statement. If I'm not mistaken, Oracle is the only one I'm aware of that does.

INSERT INTO message VALUES(...)
INSERT INTO message_content VALUES(...)

However, you can use a transaction and have both of them be contained within one transaction.

START TRANSACTION;
INSERT INTO message VALUES ('1','2','3');
INSERT INTO message_content VALUES ('bob','smith');
COMMIT;

Font: INSERT - Documentation MySQL

TRANSACTION - Documentation MySQL

Lucas Henrique
  • 1,380
  • 1
  • 11
  • 15