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

Recommended Answers

All 5 Replies

I dont think so.

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


Thanks a lot.....

I dont think so.

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:

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
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`)
IF NPSUP = 2 Then
call AddPSUPort(SID);
end if;
Be a part of the DaniWeb community

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