Is it possible to insert data in two different table using single mysql query without transaction?

Question

I know this is possible using transaction but I want to do it using single mysql query.

this is which common format transaction

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

but I need to know a single mysql query is possible?

Do you really need to do this in a single query, or in a single transaction? — Mark Baker, Apr 07 '14 at 18:05
It can't be done in one query in MySql. Just use `start transaction` then two `inserts` and `commit` at the end if you are using InnoDb. — krokodilko, Apr 07 '14 at 18:05

score 1 · Answer 1 · answered Apr 07 '14 at 18:11

1

No, it can't be done in single statement like

insert into table1,table2

either you do separately like

insert into table1 ...

insert into table2 ...

(OR)

Wrap the insert statements in stored procedure and call that procedure like

create procedure sp_insert_multiple
as
begin
    insert into table1 ...

    insert into table2 ...
end

Call the SP

exec sp_insert_multiple

answered Apr 07 '14 at 18:11

Rahul

Not two query , only one insert query – Shafiqul Islam Apr 07 '14 at 18:12
1

@ShafiqulIslam, you can't insert values to multiple table in single insert statement. – Rahul Apr 07 '14 at 18:13

Faysal Ahammed · Answer 2 · 2017-03-14T06:21:40.010

1

Normally it is not possible to insert multiple table in single query. you can insert multiple row in a single table . like as

INSERT INTO tbl_test
(a1,a2,a3)
VALUES
(1,2,3),
(4,5,6),
(7,8,9);

you can do this in Oracle

by using procedure you can insert

create procedure insert_query
as
begin
insert into tbl_test1(a1,a2,a3) VALUES (1,2,3)
insert into tbl_test2 (b1,b2,b3) VALUES (1,2,3)
end

edited Mar 14 '17 at 06:21

answered Feb 09 '17 at 15:02

Faysal Ahammed

score 1 · Answer 3 · answered Mar 11 '17 at 18:27

You can't do this. However, you can use a transaction and have both of them be contained within one transaction.

START TRANSACTION;
INSERT INTO table_1 VALUES ('1','2','3');
INSERT INTO table_2 VALUES ('one','two','three');
COMMIT;

score 1 · Accepted Answer · answered Jun 07 '22 at 13:06

1

Normally, you can not do this in MySQL. But you can do this using concat query.

INSERT INTO your_table
(value1,value2,value3)
  VALUES
(a,b,c),
(d,e,f),
(i,j,k);

But this is not your question answer. so Your question answer is NO. MYSQL is not support it still now.

answered Jun 07 '22 at 13:06

score -2 · Answer 5 · edited May 23 '17 at 12:17

-2

you can do that like coz

MySQL doesn't support multi-table insertion in a single INSERT statement.

INSERT INTO NAMES VALUES(...)
INSERT INTO PHONES VALUES(...)

here is a link of detail answer..

edited May 23 '17 at 12:17

Community

answered Apr 07 '14 at 18:06

Haroon

@Haaron, but this answer says `you can't do it in MySql, but you can do it in Oracle`. Sure, in Oracle it's possible, but the question is about MySql. – krokodilko Apr 07 '14 at 18:09
Kodirko yes thats true you cant do that in mySQL. – Haroon Apr 07 '14 at 18:11
Not two insert query, only one insert query – Shafiqul Islam Apr 07 '14 at 18:12
I believe another answer on the same question will work for you http://stackoverflow.com/questions/3860280/sql-insert-into-multiple-tables-in-one-query#answer-16332263 – andrew Apr 07 '14 at 18:12
Andrew its two queries basically.. As i suggested.. buddy. – Haroon Apr 07 '14 at 18:14
@Haroon yeah sorry about that, I pointed to the wrong answer, updated comment – andrew Apr 07 '14 at 18:15

5 Answers5