Member Avatar for aruti

Please tell me can we insert data in multiple table with single query in mysql.

For example i have two table with name `table1` and `table2` and fields in `table1` are 'id' auto incremented and primary key, second field is 'name' data type varchar of 25 and another table `table2` have fields 'id' auto incremented and 'address' data type varchar 55.

I want to know is it possible to add the data in the fields of `table1` and `table2` with single insert query

  • 5 Contributors
  • 5 Replies
  • 2K Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by rch1231

Dani AI

Generated

Agreeing with and : there is no single INSERT that writes to two different tables. You can, however, run two statements in one round trip (multi-statements) or encapsulate them in a stored procedure, as and suggested. The key is to keep them atomic and pass the generated key from the first insert to the second. The grammar for INSERT targets one table, and client APIs support sending multiple statements in one call when explicitly enabled. (dev.mysql.com)

If table2 is meant to reference table1, add a foreign key, then do both writes in a transaction and use LAST_INSERT_ID() to link them. Example pattern:

START TRANSACTION;

INSERT INTO table1(name) VALUES (?);
SET @t1_id := LAST_INSERT_ID();

INSERT INTO table2(table1_id, address) VALUES (@t1_id, ?);

COMMIT;

This ensures both rows succeed or neither does, and correctly captures the auto-increment id for the second insert. Inside stored programs, prefer START TRANSACTION (not BEGIN, which starts a block). Also model the relationship explicitly with a foreign key (table2.table1_id -> table1.id). (dev.mysql.com)

Alternative: if every insert into table1 must create a related row, use an AFTER INSERT trigger on table1 to write into table2 automatically. Triggers keep application code simple, but be sure the trigger logic is idempotent and aligned with your business rules. (dev.mysql.com)

Recommended Answers

All 5 Replies

Member Avatar for urtrivedi

I dont think so.

Member Avatar for MeSampath

Hi,

MySQL doesn't allow us to do that.

But as a work around, we can do by having/combining multiple sql statements separated with semi colon.

for more detail of insert syntax, have a look into;
http://dev.mysql.com/doc/refman/5.0/en/insert.html

Good luck.

Member Avatar for baki100

you can build a anonymous block or procedure to do this as well

Member Avatar for aruti

Hello,

Thanks a lot.....

I dont think so.

Member Avatar for rch1231

baki100 was right, the best way to update multiple table is with a procedure. Procedures allow you to use multiple MySQL statements separated by a ;. You can pass fields into a procedure and will probably need to declare some variables to hold data temporarily. below is a sample of a procedure I use to update several tables after a record is created. I call the procedure like this:

call addserver(new.server_id, new.label, new.rack_id, new.client_id, new.Number_PSU)

and here is the actual procedure:

begin
declare SOID INT;
declare ROID INT;
declare COID INT;
declare PSUOID INT;
declare PSUPOID INT;
declare NICOID INT;
declare NICPOID INT;
insert into objects (`object_type_id`, `device_object_id`, `label`, `date_created`)
values(1 , SID, SLAB, now());
set SOID=Last_Insert_ID();
SELECT ObjIDClient.object_auto_id
FROM ObjIDClient
WHERE ObjIDClient.client_id = CID into COID;
SELECT ObjIDRack.object_auto_id
FROM ObjIDRack
WHERE ObjIDRack.rack_id =  RID into ROID;
insert into objects (`object_type_id`, `device_object_id`, `label`, `date_created`)
values(26 , 0, 'PSU', now());
set PSUOID=Last_Insert_ID();
insert into objects (`object_type_id`, `device_object_id`, `label`, `date_created`)
values(43 , 0, 'PSU Power Port', now());
set PSUPOID=Last_Insert_ID();
insert into objects (`object_type_id`, `device_object_id`, `label`, `date_created`)
values(16 , 0, 'NIC', now());
set NICOID=Last_Insert_ID();
insert into`object_objects` (`parent_object_id` ,  `object_id`)
values(ROID, SOID),(COID, SOID),(SOID,PSUOID),(SOID,NICOID),(PSUOID,PSUPOID),(20127,PSUPOID);
IF NPSUP = 2 Then
call AddPSUPort(SID);
end if;
end
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.